Wizard Wizard - 3 months ago 9
MySQL Question

What kind of flags/permissions implementations should be?

I have a problem. How should I implement permissions, that can be constantly added/changed ?

Now I have this:


  • my database table scheme has field
    flags

  • I can select what I need via, for example,
    SELECT * from some_table WHERE flags&'.FLAG_CLOSED|FLAG_ACTIVE.'='.FLAG_CLOSED|FLAG_ACTIVE;

  • all flags are constants inside the code



sample code, just for figuring out what I mean (it's not real code, it's even not tested):

class Sample {

const FLAG_ACTIVE = 0x01;
const FLAG_CLOSED = 0x02;
const FLAG_DELETED = 0x04;

private $db;

public function __construct(...) {
/* ... */
}

public function getClosed() {
$flags = self::FLAG_ACTIVE | self::FLAG_CLOSED;
$query = 'SELECT * FROM `test` WHERE `flags`&'.$flags.'='.$flags;
$stmt = $this->db->query($query);
return $stmt->fetch(\PDO::FETCH_ASSOC);
}
}


It's easy to get filtered result via just one field compare, but it's so annoying to change it inside the code.

Another way to that is to make additional table, that should have flag and value. But what will be more efficient? Should value have a bit-field-based value (i.e. 1, 2, 4, 8, .. etc) or something else, like 'CLOSED', 'ACTIVE' ?

In this two cases table row will looks like:

id, description, flags
===============
1, 'test', 0x12
1, 'test', 'CLOSED,ACTIVE'


in the second case I need to filter by `WHERE flags like '%CLOSED%' and flags like '%ACTIVE%' ?

Oh.. here is the another way to implements flags. To make a table that has an
Id
and
Flag
. Like:

Id, flag
========
1, CLOSED
1, ACTIVE


may be here is exists more efficient implementation ?

p.s. sry for inconvenience, it's first question, be patient

p.p.s. I don't need to repeat this scheme exactly, I've just request an advice, how do that with more efficient way, it can be absolutely simple implementation without any bit-fields values.

Answer

Don't use a single flag field for this, use a table with a separate row for each flag.

CREATE TABLE flags (
    thing_id INT,
    flag VARCHAR(32),
    PRIMARY KEY (thing_id, flag),
    FOREIGN KEY (thing_id) REFERENCES things (id),
    INDEX (flag)
);

Rows will look like:

thing_id flag
1       active
1       closed
2       closed
3       deleted
3       active

Then to find all the things with a particular flag, you join the table:

SELECT t.* 
FROM things AS t
JOIN flags as f ON f.thing_id = t.id
WHERE f.flag = 'closed';

To get all of a thing's flags, use GROUP_CONCAT

SELECT t.*, GROUP_CONCAT(f.flag) AS flags
FROM things AS t
JOIN flags as f ON f.thing_id = t.id
WHERE t.id = :thing_id

If you want to prevent creating flags that shouldn't exist, you could make the flag column a foreign key into a flag_names table.

Comments