Wizard Wizard - 1 year ago 65
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

  • I can select what I need via, for example,

  • 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
. Like:

Id, flag

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 Source

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

    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:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download