|
|
Posted by Jerry Stuckle on 05/14/06 22:51
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...
>
> What do you reckon.
>
> TIA
>
> - Nicolaaas
>
Option A is nice because everything is in a single table. However, as others
have pointed out, it doesn't allow for any flexibility. Adding another field
(or changing an existing one) means altering the table, which should be avoided
when possible.
Option B is interesting because it compacts the data nicely. And if you use an
INT, you can have up to 32 bits. However, you won't be able to index on the
field, and if you need to search for any records with bit 5 (for instance),
MySQL will need to do a table scan.
I wouldn't even consider Option C. It's no better then Option A, and since
you're basically creating another table with a bad design, you're probably even
worse.
I agree completely with Bent. Create two additional tables, one with the option
and the second being a multi-multi link.
A link table is almost always the way to go when you have multi-to-multi links
like this.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|