473,883 Members | 1,712 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
39 3246
An exception should be noted that a DBA can only plan ahead so much
when given a customer that doesn't know what they want and doesn't
listen to the DBA. :)

"I want A". Ok.
"No, A+B". Hm, Ok, not that big a problem.
"A+B, with a little C". You can't have C with B. "We have to have C."
I told you at the start you couldn't have B with C starting with A. "We
must have C". Fine, C will be forced in.
"Actually, just C and B, not so much A". But C requires A! "So, make
it not require A, we don't need A, so C shouldn't either" Fine,
whatever, A is gone.
"Lets go with A and C, we've dropped B completely." AAAGGH!!

Just a bit of venting from having a alpha application used as release
quality despite my repeated protests that it should not be used as such.

May 16 '06 #21
Richard Levasseur wrote:
An exception should be noted that a DBA can only plan ahead so much
when given a customer that doesn't know what they want and doesn't
listen to the DBA. :)

"I want A". Ok.
"No, A+B". Hm, Ok, not that big a problem.
"A+B, with a little C". You can't have C with B. "We have to have C."
I told you at the start you couldn't have B with C starting with A. "We
must have C". Fine, C will be forced in.
"Actually, just C and B, not so much A". But C requires A! "So, make
it not require A, we don't need A, so C shouldn't either" Fine,
whatever, A is gone.
"Lets go with A and C, we've dropped B completely." AAAGGH!!

Just a bit of venting from having a alpha application used as release
quality despite my repeated protests that it should not be used as such.


Richard,

That's true. But it's up to the Project Manager (which is an entirely different
position than DBA, although one person may fill both jobs) to determine those needs.

It takes a lot of work with the customer - understanding the customer's business
(at least the area to be affected by the system), what the customer is really
thing to accomplish and a bunch of other things. And no, sometimes the customer
doesn't know these things - so the PM heeds to help the customer educate himself.

Too often I've seen a "Don't tell me - I know what you want" attitude by PM's.
Even worse is the "I know your business better than you know your business"
attitude.

People who don't take time to learn the real needs of the customer and how those
needs should be satisfied aren't doing the customer any favors. Sure, you can
get by on a website because you can rewrite it in a few hours. And eventually
you can say it did what the customer wanted.

But how often can you come away and say it truly did what the customer NEEDED?
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
May 16 '06 #22
Jerry Stuckle wrote:
A good DBA plans ahead.
....and a wise man learns to expect the unexpected.

For instance - try a database with over 75 tables, all interrelated and
linked
with foreign keys. Over 500K LOC working with this database. Changing a
table,
even to add a column, is NOT necessarily a minor change. Lots for code to
check through.


Ouch, sorry to hear that again, perhaps you should examine your architecture
and your tools? The good news is this kind of ossification can be cured,
but there is a lot of psychology involved, the patient has to *want* to
change.

Cheers,

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )
May 16 '06 #23
Kenneth Downs wrote:
Jerry Stuckle wrote:
A good DBA plans ahead.


...and a wise man learns to expect the unexpected.

For instance - try a database with over 75 tables, all interrelated and
linked
with foreign keys. Over 500K LOC working with this database. Changing a
table,
even to add a column, is NOT necessarily a minor change. Lots for code
to check through.


Ouch, sorry to hear that again, perhaps you should examine your
architecture
and your tools? The good news is this kind of ossification can be cured,
but there is a lot of psychology involved, the patient has to *want* to
change.


Can't help wonder why you would you say something like that. Are you trying
to make him feel insulted, or just joking because you don't believe putting
a little effort in the database-design process is worth it or possible in
the way Jerry presents it. Either way, if there is a point, could you be a
little more direct please.

/Bent
May 16 '06 #24
Bent Stigsen wrote:

Can't help wonder why you would you say something like that. Are you
trying to make him feel insulted, or just joking because you don't believe
putting a little effort in the database-design process is worth it or
possible in the way Jerry presents it.


None of the above. I'm laughing at Jerry's close-mindendess.

It appears that Jerry doesn't know how much he doesn't know, and if that is
true then he would not feel insulted by anything I say.

As for being more direct, I made my points in the earlier posts. Planning
is good, I do it every day, we should always learn to do it better, but a
wise man learns to expect the unexpected. The only system that doesn't
change is a dead system.

Cheers,
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )
May 16 '06 #25
Kenneth Downs wrote:
Bent Stigsen wrote:

Can't help wonder why you would you say something like that. Are you
trying to make him feel insulted, or just joking because you don't believe
putting a little effort in the database-design process is worth it or
possible in the way Jerry presents it.

None of the above. I'm laughing at Jerry's close-mindendess.

It appears that Jerry doesn't know how much he doesn't know, and if that is
true then he would not feel insulted by anything I say.

As for being more direct, I made my points in the earlier posts. Planning
is good, I do it every day, we should always learn to do it better, but a
wise man learns to expect the unexpected. The only system that doesn't
change is a dead system.

Cheers,


No, the system which doesn't change is a well-planned system.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
May 16 '06 #26
Jerry Stuckle wrote:
Kenneth Downs wrote:
Bent Stigsen wrote:

Can't help wonder why you would you say something like that. Are you
trying to make him feel insulted, or just joking because you don't
believe putting a little effort in the database-design process is worth
it or possible in the way Jerry presents it.

None of the above. I'm laughing at Jerry's close-mindendess.

It appears that Jerry doesn't know how much he doesn't know, and if that
is true then he would not feel insulted by anything I say.

As for being more direct, I made my points in the earlier posts.
Planning is good, I do it every day, we should always learn to do it
better, but a
wise man learns to expect the unexpected. The only system that doesn't
change is a dead system.

Cheers,


No, the system which doesn't change is a well-planned system.


Brent: QED.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )
May 16 '06 #27
Kenneth Downs wrote:
Jerry Stuckle wrote:

Kenneth Downs wrote:
Bent Stigsen wrote:

Can't help wonder why you would you say something like that. Are you
trying to make him feel insulted, or just joking because you don't
believe putting a little effort in the database-design process is worth
it or possible in the way Jerry presents it.
None of the above. I'm laughing at Jerry's close-mindendess.

It appears that Jerry doesn't know how much he doesn't know, and if that
is true then he would not feel insulted by anything I say.

As for being more direct, I made my points in the earlier posts.
Planning is good, I do it every day, we should always learn to do it
better, but a
wise man learns to expect the unexpected. The only system that doesn't
change is a dead system.

Cheers,


No, the system which doesn't change is a well-planned system.

Brent: QED.


Sorry, I can't help it if you don't know how to plan a system.

I'm not saying none of my designs have never changed. Sure, some of them have.
But the vast majority have never had the database changed, despite increased
needed functionality.
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
May 16 '06 #28
Kenneth Downs wrote:
Bent Stigsen wrote:

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.

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


As I said, in this case it may be valid.


Perhaps we think the same, but just to be clear. No doubt all suggested
solutions will get the job done. In that respect, I consider them of equal
validity. But I do consider the solution with the intermediate link table a
better option, especially since it saves a hell of a lot of work, should
the requirements change.
It would be a move toward using E-A-V if and only if it were done
specifically to avoid structure changes. That would flag a mindset that
would tend towards making the data more and more difficult to work with,
trading the effort of regular development and use for the effort of
modifying the table structures.
My only objection is "more and more difficult". It is not like abstraction
is something that demand more and more by itself, but yes, not unlikely to
be a trade, but which I wouldn't automatically think of as difficult, not
in this case anyway.

Sure, it can be seen as increased complexity and abstraction, allthough I
don't consider it a big deal, but rather just part of the natural design
process. I'll get back to that.
I can only argue here from experience. A flag is a property of the entity
being recorded in the table. That means by default it is a column in a
table, along with other flags. This is the simplest possible arrangement
and anything else carries a higher ongoing cost. The default position is
to have it a column in the table. In most cases, if the customer wants
another flag, that's another column. If a developer is trying to avoid
structure changes because of cost, then that developer needs to seriously
look at their development tools (or coding habits), anything which drives
you away from the natural use of tables is not your friend.


I would contend that it is not impossible, actually well within classical
relational design, to make a database less likely to require altering in
the future, without resorting to complex constructs with higher developing
costs as result. It is not just a hopeful idea. Since Jerry's word
apparently is not good enough, I'll try argument.

I think the original question is a fine example. OP's initial approach is,
as you also imply, a set of properties of the entity, and you conclude
therefor should be represented in the table of the entity. We could stop
here, not making any closer analysis of the data or playing what-if, and
from this point of view you are quite right. (except claiming that
"anything else carries a higher ongoing cost.")
But the OP does go a little further as he just generally calls them
characteristics . Even without making any assumption about the future, it
wouldn't be strange to elevate a characteristic as an entity by itself,
from thereon ordinary normalization *dictates* a separate entity-table and
an intermediate link table because of the many-to-many relation. That is
not cheating or making unnecessary abstractions, it is just plain database
design.
You can get the same result if you consider the somewhat flaky properties
("with restaurant", "drive-through facility", etc. ). It is not exactly
unthinkable that another characteristic comes along after a couple of
months, which nobody had thought of. I would count on it.

If or when that extra requirement comes along, the solution with one field
to each property would require changes through the whole system, from
schema to interface, whereas the other solution would at worst require
minor adjustment in the userinterface for displaying an extra option.
Clearly not "a higher ongoing cost".
/Bent

May 16 '06 #29
On 14 May 2006 03:33:01 -0700, in comp.lang.php , "windandwav es"
<nf*******@gmai l.com> in
<11************ **********@j73g 2000cwa.googleg roups.com> 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


Code is more expensive than storage.
--
Matt Silberstein

Do something today about the Darfur Genocide

http://www.beawitness.org
http://www.darfurgenocide.org
http://www.savedarfur.org

"Darfur: A Genocide We can Stop"
May 16 '06 #30

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

Similar topics

125
14927
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
3541
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 that are headed in various categories and are yes/no formated
11
4445
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 = split(request.form("impr"),",") idate =...
0
9933
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 usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
11123
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10407
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7114
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4606
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4211
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.