Danmorgan.net

I have seen too many times where people struggle and toil with customizing database tables to fit every small aspect of their data models. Many times they end up using many tables because they have dynamic data relationship needs.

However this may just simply be overkill.

If you are going to store data that is not going to appear in a WHERE clause then you can store it arbitrarily. And by arbitrary I simply mean that you can store PHP data objects themselves into the database.

In your table you wish to store the arbitrary data just create a field that is BLOB. For the following example the field ‘arb_data’ is set to a BLOB.

$objects = array("session"=>$session,"cache"=>$cache,"user"=>$user);
$arb_data = mysql_escape_string(gzdeflate(serialize($objects)));
$query = "INSERT into my_table (id,arb_data) values ({$id},'{$arb_data}')";
mysql_query($query);

This will serialize and gzip the data objects then insert them into the BLOB field. To retrieve the data we do the opposite :

$query = "SELECT arb_data from my_table where id = {$id}";
$q = mysql_query($query);
$res = mysql_fetch_array($q);
$objects = unserialize(gzinflate($res["arb_data"]));
var_dump($objects);

This will obviously not work if you need to have your queries conditional on the data stored as arbitrary data (i.e. If arb_data doesn’t need to be in WHERE,ORDER BY, JOIN, etc..).

Tracking Multiple Bit Wise Flags in MySQL.

Per say you are tracking multiple states of a record. A record can have 4 distinct states :

1st – New
2nd – Process Failed
3rd – Process Successful, but Unrendered
4th – Rendered (Success)

Now lets say there are 3 distinct groups that we are tracking these states for per record.

So we now have 4 states, within 3 groups. Each group needs to be independent from each other, but each group can only have one state set per record.

So a Record would have the following groups:

Group A
Group B
Group C

We can represent this information with bit wise operators in an INT field.

Lets start by dividing up each group into binary switches.

A bit can be on or off, so it has 2 settings. Since we have 4 distinct states we would use two bits( 2 ^ 2).

Bit 1 = Off, Bit 2 = Off : 1st State
Bit 1 = Off, Bit 2 = On : 2nd State
Bit 1 = On, Bit 2 = Off : 3rd State
Bit 1 = On, Bit 2 = On : 4th State

Now we are going to represent these bits with numeric values. Remember we want to keep these states separated from each other so we are going to use different bits for each group.

Group A
Bit 1 = 1
Bit 2 = 2

Group B
Bit 3 = 4
Bit 4 = 8

Group C
Bit 5 = 16
Bit 6 = 32

We now have a binary map of each of our states for each of our groups. In our database we are going to call this GroupsState.

Here is a map of each state in each group with its numeric representation :

GroupA
0:  1st – New
1:  2nd – Process Failed
2:  3rd – Process Successful, but Unrendered
3:  4th – Rendered

GroupB
0:  1st – New
4:  2nd – Process Failed
8:  3rd – Process Successful, but Unrendered
12: 4th – Rendered

GroupC
0:  1st – New
16: 2nd – Process Failed
32: 3rd – Process Successful, but Unrendered
48: 4th – Rendered

To select our state from the database we would compare this value against the bit wise operator of AND to both bits:

SELECT (GroupState & 3) as GroupA,(GroupState & 12) as GroupB,(GroupState & 48) as GroupC from records

This would give you the numeric representation of the state for each group as its name in the results. If you want to query all successful GroupA records you would use the same bitwise operator :

SELECT * from records where (GroupState & 3) = 3

Or if you wanted all failed processes from any group you would :

SELECT * from records where (GroupState & 21) > 0

adding up all processed failed numeric vaules we come to 21.

Good So far?

Now say we want to change a state of a group, how do we do that? We need to turn off the bits to the group first, then turn them back up to what they need to be. This is done by using INVERTED AND to turn them off, and then OR to turn them back up.

Per say we have re-queried a record with the Process Failed state in GroupC and we are now going to update it to a Rendered (Success), our query would look like this :

UPDATE records set GroupState = ((GroupState &~ 48) | 48) where id = 5;

This would turn both bits in GroupC off, then would kick both on. This is handy because you may not know if the current state of GroupC.

So how would we change Group B from “Process Failed” to “Process Successful, but Unrendered” ?

UPDATE records set GroupState = ((GroupState &~ 12) | 8 ) where id = 5;

12 to turn off both bits in GroupB, and turn up the 8 to signify the 3rd state.

This type of data handling can be very useful when you are reconciling a large amount of data and need to know what steps have been completed.