473,573 Members | 2,788 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

May 14 '06 #1
39 3202
"windandwav es" <nf*******@gmai l.com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. .
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...


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
May 14 '06 #2
Carved in mystic runes upon the very living rock, the last words of
windandwaves of comp.lang.php make plain:
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


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

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/
May 14 '06 #3
Alan Little wrote:
Carved in mystic runes upon the very living rock, the last words of
windandwaves of comp.lang.php make plain:
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


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


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.
May 14 '06 #4
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?


--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )
May 14 '06 #5
On 2006-05-14, windandwaves <nf*******@gmai l.com> wrote:
There are three ways of storing this information in my mysql database
A. add eight fields (tiny integer)
If those eight fields are 'properties' that belong to each item, then
this seems like a good approach.
B. add one tiny integer and create a function in PHP that can translate
the number stored into a eight boolean values (the bits)
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 ;)
C. create a table which list
ItemID
Associated characteristics
This seems like the right approach for n-m relationships.
Option B is the most efficient in MySql.


Define efficient.
--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be >
May 14 '06 #6
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
May 14 '06 #7
On Sun, 14 May 2006 11:34:05 +0000, Colin McKinnon wrote:
Alan Little wrote:
Carved in mystic runes upon the very living rock, the last words of
windandwaves of comp.lang.php make plain:
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


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


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.

....also when the customer decides they want a 9th value stored? Waaay too
inflexible.

I'd use option D

May 14 '06 #8
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.
js*******@attgl obal.net
=============== ===
May 14 '06 #9
Bent Stigsen wrote:


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).


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 )
May 14 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

125
14607
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from software giant such as Microsoft SQL Server, Oracle, and Sybase? Is PostgreSQL reliable enough to be used for high-end commercial application? Thanks
13
3507
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to make a query that selects from a table as desribed below .. I have a table (Volunteer) that has a member field (memnumber) and a number of fields ...
11
4408
by: pmarisole | last post by:
I am trying to use the vbscript "split" function on a multi-select field. I am trying to do a mass update of several records at a time. I am getting an error and I'm not sure what to do. Here is the code if someone could help... strID = split(request.form("proj"), ", ") projstat = split(request.form("rojstat"),",") impr...
0
7746
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7986
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7736
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8036
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6358
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5556
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5258
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3699
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.