tags:

views:

1321

answers:

7

I'm writng a small application in PHP + MySQL and have come to the point where there is an object that has a couple (8 so far but not expected to increase) of flags associated with it. The flags are pretty much unrelated although there are some combinations that would make no sense. The object represents a row in a DB (has some methods for saving/loading it too) so the question also applies to choosing a storage method.

The question is - how to best represent them both in code and DB? I can think of several ways:

One way to store them in the DB is in a single integer field as bitwise flags. On the PHP side then I can imagine several ways of representing them:

  • Simply export the integer value and define a couple of flag constants; Let every place that needs the flags do their own bitwise magic;
  • Define class methods GetFlag(), SetFlag() and UnsetFlag() that do the bitwise magic on a private integer variable; These methods would then be passed one of the flag constants as a parameters.
  • Define methods GetFlagA(), GetFlagB() etc. (along with Set and Unset counterparts);
  • Define a bunch of member variables that each represent one flag; Set them on loading from the DB and gather them when saving.
  • Create a member variable that is an array of all the flag values. Use predefined constants as array indices to access each flag. Also fill/gather the array on loading/saving.

Another way would be to store them in the DB as separate BIT fields. In PHP that would then translate to several member variables. IMHO this would complicate the queries.

And the last way would be to define anothed table for all the flags and an intermediate table for many-to-many relationship between flags and the original objects. IMHO the messiest of all solutions, considering that there would be only like 3 tables otherwise.

I haven't done much PHP development so I don't know what the best practice would be. In C# I would probably store them as bitwise flags and make properties that do the bitwise magic on a private integer. But PHP doesn't have properties (I'm using latest stable version)...

A: 

Assuming your using a version of MySQL after 5.0.5 you could define the column as BIT[number of bits here]. As for the PHP side I would probably go with the Get/SetFlagA, Get/SetFlagB approach, there is no need for an unset method as you can simply take in a boolean to the set method.

Kevin Loney
A: 

I would stay away from the bitwise operations as it becomes very hard to know which flags are set by looking only at the database (unless you are trying to be super efficient for some reason). Between the other two options I think it depends, on how much meaning each of these flags have. Given that there are already 8 of them I would lean towards another table with a many to many relationship between them (sorry for picking your least favorite).

Brian Fisher
+1  A: 

If you really have to use bit flags, then use a SET column to store them in the DB, an associative array in the code, and methods to turn flags on/off. Add separate methods to convert the array to/from a single integer if you need it in that format.

There's nothing to be gained by using low-level bit operators, so you might as well make the code readable.

Ant P.
Exactly. The question just is - what IS more readable? Plus, I'd like to avoid defining the values DB-side, because then any changes would have to be reflected both in DB and PHP. Changes in bitwise flags would be PHP-only.
Vilx-
If you've looked at both methods and can't determine which is more readable, then it seems like the decision's arbitrary. Try showing examples to other developers or imagine that you have to re-familiarize yourself with the code long after you've forgotten it to get a better idea of readability.
Phantom Watson
Vilx: Not so. Changes will have to be reflected in the database under either method.
Preston
A: 

One way to store them in the DB is in a single integer field as bitwise flags.

If you wanted to do this, you could use the __get and __set overload methods so you can simply retrieve the field, and then do the bitwise arithmetic as needed.

Zoredache
Yes, I forgot to mention this possibility. It's another one I don't like because it pretty much messes up code completion in IDEs and IMHO makes the code even less readable.
Vilx-
A: 

OK, after thinking some more about it I've decided to go with one member variable per flag. I could've used the getter/setter method approach, but I don't use them anywhere else in my code so this would be out-of-style. Plus, this way I also abstract away from DB storage method and can later easily change that if needed.

As for the DB - I'm going to stay with the bitwise integer for now - mainly because I'm almost done with the software and don't want to change that anymore. It doesn't change readability at all.

Vilx-
A: 

In your model, the object has 8 boolean properties. That implies 8 boolean (TINYINT for MySQL) columns in your database table and 8 getter/setter methods in your object. Simple and conventional.

Rethink your current approach. Imagine what the next guy who has to maintain this thing will be saying.

CREATE TABLE mytable (myfield BIT(8));

OK, looks like we're going to have some binary data happening here.

INSERT INTO mytable VALUES (b'00101000');

Wait, somebody tell me again what each of those 1s and 0s stands for.

SELECT * FROM mytable;
+------------+
| mybitfield |
+------------+
| (          | 
+------------+

What?

SELECT * FROM mytable WHERE myfield & b'00101000' = b'00100000';

WTF!? WTF!?

stabs self in face


-- meanwhile, in an alternate universe where fairies play with unicorns and programmers don't hate DBAs... --

SELECT * FROM mytable WHERE field3 = 1 AND field5 = 0;

Happiness and sunshine!

Preston
A: 

I coded this simple function to fill the gap between PHP and MySQL ENUMs:

function Enum($id)
{
    static $enum = array();

    if (func_num_args() > 1)
    {
        $result = 0;

        if (empty($enum[$id]) === true)
        {
            $enum[$id] = array();
        }

        foreach (array_unique(array_map('strtoupper', array_slice(func_get_args(), 1))) as $argument)
        {
            if (empty($enum[$id][$argument]) === true)
            {
                $enum[$id][$argument] = pow(2, count($enum[$id]));
            }

            $result += $enum[$id][$argument];
        }

        return $result;
    }

    return false;
}

Usage:

// sets the bit flags for the "user" namespace and returns the sum of all flags (15)
Enum('user', 'anonymous', 'registed', 'active', 'banned');

Enum('user', 'anonymous'); // 1
Enum('user', 'registed', 'active'); // 2 + 4 = 6
Enum('user', 'registed', 'active', 'banned'); // 2 + 4 + 8 = 14
Enum('user', 'registed', 'banned'); // 2 + 8 = 10

You just have to make sure you define the enumerated list in the same order of MySQL ENUM.

Alix Axel