what is better - one field or eight - mysql bit testing

[windandwaves]

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

[Dana Cartwright]

Re: what is better - one field or eight - mysql bit testing

"windandwav es" <nfrancken@gmai l.com> wrote in message
news:1147602781 .523079.191310@ j73g2000cwa.goo glegroups.com.. .[color=blue]
> I have to store up to eight boolean bits of information about an item
> in my database.
> 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...[/color]

Do you want to know which of these options is "best"? If so, you need to
define "best". And if you define "best", I suspect you'll have answered
your own question.

If you simply want something that works, flip a coin. They all "work".
Your biggest challenge in this case would be to find a 3-sided coin, of
course.

My experience with projects suggests strongly that your current eight fields
will grow in number. This always seems to happen. So you might consider
which of these schemes is the easiest to grow. Note that "B" is tricky
because it doesn't scale up very well (you run out of bits fairly quickly).

Also, consider a more philosophical question: why are you considering
storing the information as bits? I assume the answer is "because it's
possible to do so". What if your eight characteristics were not boolean?
What if they were, say, ternary? In that case, you would probably not store
them as bits, even though ternary data does, in the final analysis, always
get stored as bits. Ask yourself whether it makes sense to change the
storage method just because your particular characteristics are boolean?

-- Dana

[Alan Little]

Re: what is better - one field or eight - mysql bit testing

Carved in mystic runes upon the very living rock, the last words of
windandwaves of comp.lang.php make plain:
[color=blue]
> I have to store up to eight boolean bits of information about an item
> in my database.
>
> 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[/color]

I'd go with B, just my personal preference.

--
Alan Little
Phorm PHP Form Processor

http://www.phorm.com/

[Colin McKinnon]

Re: what is better - one field or eight - mysql bit testing

Alan Little wrote:
[color=blue]
> Carved in mystic runes upon the very living rock, the last words of
> windandwaves of comp.lang.php make plain:
>[color=green]
>> I have to store up to eight boolean bits of information about an item
>> in my database.
>>
>> 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[/color]
>
> I'd go with B, just my personal preference.
>[/color]

You'd get a mouthful if you tried that working for me.

Its a BAD idea. SQL has no visibility of it. It's not normalized and it
doesn't scale. In fairness certain types of search can be done very fast,
but they're unlikely to be particularly common.

C.

[Kenneth Downs]

Re: what is better - one field or eight - mysql bit testing

windandwaves wrote:
[color=blue]
> 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...
>[/color]

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?




--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )

[Tim Van Wassenhove]

Re: what is better - one field or eight - mysql bit testing

On 2006-05-14, windandwaves <nfrancken@gmai l.com> wrote:[color=blue]
> There are three ways of storing this information in my mysql database
> A. add eight fields (tiny integer)[/color]

If those eight fields are 'properties' that belong to each item, then
this seems like a good approach.
[color=blue]
> B. add one tiny integer and create a function in PHP that can translate
> the number stored into a eight boolean values (the bits)[/color]

I don't believe in such 'fields'. They only 'seem' to be the most
efficient, but only when there are as much possible options as there are
bits. (Eg: 9 options would require 2 x 8 bits (and 7 are left unused))

Anyway, mysql seems to have special 'field' functions these days, check
out the manual ;)
[color=blue]
> C. create a table which list
> ItemID
> Associated characteristics[/color]

This seems like the right approach for n-m relationships.
[color=blue]
> Option B is the most efficient in MySql.[/color]

Define efficient.


--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be >

[Bent Stigsen]

Re: what is better - one field or eight - mysql bit testing

Kenneth Downs wrote:
[color=blue]
> windandwaves wrote:
>[color=green]
>> 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...
>>[/color]
>
> 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?[/color]

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

[Steve]

Re: what is better - one field or eight - mysql bit testing

On Sun, 14 May 2006 11:34:05 +0000, Colin McKinnon wrote:
[color=blue]
> Alan Little wrote:
>[color=green]
>> Carved in mystic runes upon the very living rock, the last words of
>> windandwaves of comp.lang.php make plain:
>>[color=darkred]
>>> I have to store up to eight boolean bits of information about an item
>>> in my database.
>>>
>>> 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[/color]
>>
>> I'd go with B, just my personal preference.
>>[/color]
>
> You'd get a mouthful if you tried that working for me.
>
> Its a BAD idea. SQL has no visibility of it. It's not normalized and it
> doesn't scale. In fairness certain types of search can be done very fast,
> but they're unlikely to be particularly common.
>
> C.[/color]
....also when the customer decides they want a 9th value stored? Waaay too
inflexible.

I'd use option D

[Jerry Stuckle]

Re: what is better - one field or eight - mysql bit testing

windandwaves wrote:[color=blue]
> 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
>[/color]

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@attgl obal.net
=============== ===

[Kenneth Downs]

Re: what is better - one field or eight - mysql bit testing

Bent Stigsen wrote:
[color=blue]
>
>
> 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).[/color]

Anybody working with databases must have a reasonable method for altering
table structures as a regular event and a reasonable way to synchronize
structures and the code that works with them. Not having this will cost,
and all solutions that seek to re-invent physical implementation produce
burdens worse than the disease.

The solution you present may be correct in his case, but it verges on the
dreaded 'abstraction' of the E-A-V system, and if that is so it would be a
cure worse than the disease. Whether it is a valid cross-reference or an
instance of E-A-V would require knowing more about the system.

But I would repeat that any 'abstraction' made in an attempt to avoid table
structure changes is going to fail. It fails because you give up what the
server can do for you and end up spending your time reinventing an RDMBS
server.


--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )

[Richard Levasseur]

Re: what is better - one field or eight - mysql bit testing

FYI - The proper term for a table that solves a many-to-many
relationship is called an 'intersection tabel'

items(item_id, name);
items2chars(ite m_id, char_id); <-- intersection table
characteristics (char_id, name);

In any event, don't store the characteristics in a single field. Put
them in seperate fields, or create a characteristics table with an
intersection table.

If a characteristics is only a boolean value, and you don't foresee
adding/removing attributes, then using fields would work fine (though
it may make a couple of complicated queries a little tricky).
If characteristics have attributes themselves, then you need to use the
intersection method.
If characteristics you foresee characteristics beinga added/removed, it
would be better to use the intersection method.

[Jerry Stuckle]

Re: what is better - one field or eight - mysql bit testing

Kenneth Downs wrote:[color=blue]
> Bent Stigsen wrote:
>
>[color=green]
>>
>>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).[/color]
>
>
> Anybody working with databases must have a reasonable method for altering
> table structures as a regular event and a reasonable way to synchronize
> structures and the code that works with them. Not having this will cost,
> and all solutions that seek to re-invent physical implementation produce
> burdens worse than the disease.
>[/color]

I disagree. If you properly plan your installation, you will not need to change
your tables. Over the years I've designed hundreds of databases; most of them
have never been changed.

Having to alter a database layout either means you've had a significant change
in the database needs, or, more likely, you didn't design it properly in the
first place.
[color=blue]
> The solution you present may be correct in his case, but it verges on the
> dreaded 'abstraction' of the E-A-V system, and if that is so it would be a
> cure worse than the disease. Whether it is a valid cross-reference or an
> instance of E-A-V would require knowing more about the system.
>[/color]

Not at all. In fact, it makes things much easier to handle in the long run.
When you have a multi-to-multi connections, as in this case, a third table is
almost always the way to go.

[color=blue]
> But I would repeat that any 'abstraction' made in an attempt to avoid table
> structure changes is going to fail. It fails because you give up what the
> server can do for you and end up spending your time reinventing an RDMBS
> server.
>
>[/color]

I completely disagree. Again, many times I've abstracted things yet made good
use of the server's abilities.

Abstraction only means you're separating the database structure from the program
logic. This is generally considered a good thing, especially as you get into
more complicated projects..



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: what is better - one field or eight - mysql bit testing

Richard Levasseur wrote:[color=blue]
> FYI - The proper term for a table that solves a many-to-many
> relationship is called an 'intersection tabel'
>[/color]

Yes intersection tabel (sic) is one name for them, but describes a subset of
link tables. Link table is another, mutli-to-multi-link table is a third. The
term "link table" has been around since the 1970's. I first heard "intersecti on
table" in the late 1990's.
[color=blue]
> items(item_id, name);
> items2chars(ite m_id, char_id); <-- intersection table
> characteristics (char_id, name);
>
> In any event, don't store the characteristics in a single field. Put
> them in seperate fields, or create a characteristics table with an
> intersection table.
>[/color]

Yes, that is the way to use a link table. You also need to set up foreign keys
on both columns in the link table.
[color=blue]
> If a characteristics is only a boolean value, and you don't foresee
> adding/removing attributes, then using fields would work fine (though
> it may make a couple of complicated queries a little tricky).
> If characteristics have attributes themselves, then you need to use the
> intersection method.
> If characteristics you foresee characteristics beinga added/removed, it
> would be better to use the intersection method.
>[/color]



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Bent Stigsen]

Re: what is better - one field or eight - mysql bit testing

Kenneth Downs wrote:
[color=blue]
> Bent Stigsen wrote:
>[color=green]
>>
>> 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).[/color]
>
> Anybody working with databases must have a reasonable method for altering
> table structures as a regular event and a reasonable way to synchronize
> structures and the code that works with them. Not having this will cost,
> and all solutions that seek to re-invent physical implementation produce
> burdens worse than the disease.
>
> The solution you present may be correct in his case, but it verges on the
> dreaded 'abstraction' of the E-A-V system, and if that is so it would be a
> cure worse than the disease. Whether it is a valid cross-reference or an
> instance of E-A-V would require knowing more about the system.[/color]

Hmmm, I don't really see it as such. Arguably a step in that direction, but
still good old relational tables to me, and very much a reasonable method
to anticipate changes in schema/data.

[color=blue]
> But I would repeat that any 'abstraction' made in an attempt to avoid
> table
> structure changes is going to fail. It fails because you give up what the
> server can do for you and end up spending your time reinventing an RDMBS
> server.[/color]

Perhaps I don't quite understand you here, but I don't think I am
reinventing anything. Using an intermediate table to represent a
many-to-many relationship is hopefully quite a common practice, and by no
means an abuse of any relational database, but rather something they are
extremely good at.


/Bent

[Kenneth Downs]

Re: what is better - one field or eight - mysql bit testing

Jerry Stuckle wrote:
[color=blue]
> Kenneth Downs wrote:[color=green]
>> Bent Stigsen wrote:
>>
>>[color=darkred]
>>>
>>>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).[/color]
>>
>>
>> Anybody working with databases must have a reasonable method for altering
>> table structures as a regular event and a reasonable way to synchronize
>> structures and the code that works with them. Not having this will cost,
>> and all solutions that seek to re-invent physical implementation produce
>> burdens worse than the disease.
>>[/color]
>
> I disagree. If you properly plan your installation, you will not need to
> change
> your tables. Over the years I've designed hundreds of databases; most of
> them have never been changed.[/color]

Sorry to hear that.
[color=blue]
>
> Having to alter a database layout either means you've had a significant
> change in the database needs, or, more likely, you didn't design it
> properly in the first place.[/color]

Or your customer loved it, their business is growing, and they've got more
stuff for you to do.

Cheers,

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )