Posted by Bent Stigsen on 05/14/06 20:33

Kenneth Downs wrote:

> windandwaves wrote:
>
>> Hi Folk
>>
>> I have to store up to eight boolean bits of information about an item
>> in my database.
>>
>> e.g.
>>
>> [ ] with restaurant
>> [ ] drive-through facility
>> [ ] yellow windows
>> [ ] wifi factilities
>> etc...
>>
>> There are three ways of storing this information in my mysql database
>> A. add eight fields (tiny integer)
>> B. add one tiny integer and create a function in PHP that can translate
>> the number stored into a eight boolean values (the bits)
>> C. create a table which list
>> ItemID
>> Associated characteristics
>> In C, you will only list the characteristics that are true for the item
>> listed.
>>
>> Option B is the most efficient in MySql, but would you recommend it
>> when creating a PHP website. The problem is that the user needs to
>> enter them with a nice webform, etc...
>>
>
> From experience? Option B obfuscates the data, and so will cost you over
> and over, far past your memory of why you thought it was a good idea when
> you
> did it. The data is now trapped in a column that requires annoying
> gymnastics to pull out, nobody will remember which column is in what bit,
> and so on and so on. Your PHP library now becomes a *requirement*, what
> happens if I am at a database console and what to do some simple ad-hoc
> queries?
>
> OTOH, Option A has 8 named columns that can be updated and queried with
> SQL, why do anything else?

With option A, consider he would want to add or remove a characteristic, he
would then have to:
- alter the table
- adjust at least some select/insert/update-statements, which has to be
identified first.
- adjust implicated code and userinterface.

Such changes can of course be anticipated in the code, but would add quite
some complexity to the code, and dynamic behavior like that would require
extra information, which also is partly redundant information as it mirrors
a structure in the database.


The answer is D, implementing two tables.
One table with the characteristics, and one table containing two foreign
keys making the association between characteristics and the "items" (what
that might be).

Extra fields can conveniently be added to the table with the
characteristics, like a description which could be handy as a helptext, if
someone should wonder what is meant by "yellow windows".


/Bent

[Back to original message]