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

"windandwaves" <nfrancken@gmail.com> wrote in message
news:1147602781.523079.191310@j73g2000cwa.googlegr oups.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

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/

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.

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)

On 2006-05-14, windandwaves <nfrancken@gmail.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>

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

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

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

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)

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

items(item_id, name);
items2chars(item_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.

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

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 "intersection
table" in the late 1990's.
[color=blue]
> items(item_id, name);
> items2chars(item_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@attglobal.net
==================

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

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)

Carved in mystic runes upon the very living rock, the last words of
Colin McKinnon of comp.lang.php make plain:
[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]
>
> 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.[/color]

Yeah, you're right. That'll teach me to post quickie answers without
thinking about it. I used such an approach in an application once, and
that was what I was thinking about, but the thing I used it for, there
was never more than 20 - 30 items and never any need to query it.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

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

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

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.

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.


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

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

My customers aren't. It's because the databases were designed properly in the
first place.
[color=blue]
>[color=green]
>>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.
>[/color]

And if it had been designed properly in the first place, you would have planned
for the growth.

You can't plan for all possible changes. But a good designer can plan ahead for
most of the potential changes and incorporate them in the database early.

A good DBA plans ahead. Those who say you should plan on changing your database
either don't plan ahead or, more likely, are just hackers throwing together code
to fix the current problem.

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.

And adding a new table can really cause problems. This is one successful
project I managed several years ago.

Even in my web sites I need to change virtually none of my database structures
after they've gone live - because I've planned ahead.
[color=blue]
> Cheers,
>[/color]


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

Jerry Stuckle wrote:
[color=blue]
> Kenneth Downs wrote:[color=green]
>> Jerry Stuckle wrote:
>>
>>[color=darkred]
>>>Kenneth Downs wrote:
>>>[/color][/color][/color]
[snip][color=blue][color=green][color=darkred]
>>>>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.
>>>>
>>>
>>>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]
>
> My customers aren't. It's because the databases were designed properly in
> the first place.[/color]

Sadly not all customers appreciate that. All some care about is the <beep>
dessert topping. I find that a trifle annoying. I utterly refuse to play
along with that. Oh what fun I would have, being a ruthless dictator. chop.
chop.


/Bent

Bent Stigsen wrote:[color=blue]
> Jerry Stuckle wrote:
>[color=green]
>>
>>My customers aren't. It's because the databases were designed properly in
>>the first place.[/color]
>
>
> Sadly not all customers appreciate that. All some care about is the <beep>
> dessert topping. I find that a trifle annoying. I utterly refuse to play
> along with that. Oh what fun I would have, being a ruthless dictator. chop.
> chop.
>
>
> /Bent[/color]

Bent,

Unfortunately, that's true. Some have to go through a messy (and expensive)
change cycle to become believers.

At the same time, I've found most databases can be designed with change in mind
very quickly. Just properly normalizing the database does wonders. And in a
case like the one which started this discussion, thinking ahead - yes, now we
have eight options. But would we ever want to add more? If so, a link table is
the way to go.

Sometimes it's not obvious. But understanding the data and how it will be used
is critical to proper database design.


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

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

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.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
[color=blue]
> A good DBA plans ahead.[/color]

....and a wise man learns to expect the unexpected.
[color=blue]
>
> 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.
>[/color]

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)

Kenneth Downs wrote:
[color=blue]
> Jerry Stuckle wrote:
>[color=green]
>> A good DBA plans ahead.[/color]
>
> ...and a wise man learns to expect the unexpected.
>[color=green]
>>
>> 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.
>>[/color]
>
> 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.[/color]

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

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

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)

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

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.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
[color=blue]
> Kenneth Downs wrote:[color=green]
>> Bent Stigsen wrote:
>>
>>[color=darkred]
>>>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.[/color]
>>
>>
>> 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,[/color]
>
> No, the system which doesn't change is a well-planned system.
>[/color]

Brent: QED.

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

Kenneth Downs wrote:[color=blue]
> Jerry Stuckle wrote:
>
>[color=green]
>>Kenneth Downs wrote:
>>[color=darkred]
>>>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,[/color]
>>
>>No, the system which doesn't change is a well-planned system.
>>[/color]
>
>
> Brent: QED.
>[/color]

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.
jstucklex@attglobal.net
==================

Kenneth Downs wrote:
[color=blue]
> Bent Stigsen wrote:
>[color=green]
>>
>> 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=darkred]
>>> 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.
>>
>>[/color]
>
> As I said, in this case it may be valid.[/color]

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

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

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

Kenneth Downs wrote:
[color=blue]
> Jerry Stuckle wrote:
>[color=green]
>> Kenneth Downs wrote:[color=darkred]
>>> 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,[/color]
>>
>> No, the system which doesn't change is a well-planned system.
>>[/color]
>
> Brent: QED.[/color]

Well then, feel free to laugh at me too. :)

I generally agree, allthough to a statement like the above, I would add a
disclaimer regarding its validity, with conditions like natural disasters,
year 10000+ bug, and other dealbreakers which could be expected. But I can
safely say I will fall short when it comes to the unexpected.

/Bent

On 14 May 2006 03:33:01 -0700, in comp.lang.php , "windandwaves"
<nfrancken@gmail.com> in
<1147602781.523079.191310@j73g2000cwa.googlegroups .com> 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[/color]

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"

Bent Stigsen wrote:[color=blue]
> Kenneth Downs wrote:
>
>[color=green]
>>Jerry Stuckle wrote:
>>
>>[color=darkred]
>>>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.
>>>[/color]
>>
>>Brent: QED.[/color]
>
>
> Well then, feel free to laugh at me too. :)
>
> I generally agree, allthough to a statement like the above, I would add a
> disclaimer regarding its validity, with conditions like natural disasters,
> year 10000+ bug, and other dealbreakers which could be expected. But I can
> safely say I will fall short when it comes to the unexpected.
>
> /Bent[/color]

That's true - but I could also claim it shows a weakness in the design.

An ideal design would never have to be changed. It would be complete and
flexible enough for anything the customer would (reasonably) want to do. Note
that I'm not saying a "perfect design" which does everything. But one which
would meet that customer's needs for years to come.

But of course we can't always create that idea design. But the better our
understanding of the customers business and needs, the closer we can come to it.
Which means the less chance things need to be changed in the future.

Some people think changing a database is a minor issue. In some cases it is -
but it can also be a huge issue. For instance - the infamous Y2K bug was
basically a database design problem. And look what it cost companies just to
change a column from 2 digits to 4.


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

Bent Stigsen wrote:
[color=blue]
> Kenneth Downs wrote:
>[color=green]
>> Bent Stigsen wrote:
>>[color=darkred]
>>>
>>> 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.
>>>
>>>[/color]
>>
>> As I said, in this case it may be valid.[/color]
>
> 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.[/color]

Solutions which appear to be of equal validity often turn out not to be.
With a little more digging we could find out. In this case we can't know
the answer, but we can define the parameters.

With a bunch of user-defined flags whose existence doesn't require
special-case programming, use a cross-reference. Of course it may turn out
nobody uses them because they don't do anything. And there is the fact
that you now need at least one JOIN every time you reference them, so I'm
not exactly sure what the "hell of a lot of work" you've saved is, looks
like you made work for yourself actually.

OTOH, the default position is that a property of an thing is built as a
column in a table, unless that property is itself another thing that has
more properties. This is the design of maximum efficiency, which is the
real reason why Codd is so famous for explaining it all to us. This is
also why my earlier posts stress that it is vital for your tools to make it
easy to change table designs. The table design is the foundation of your
system, every inefficiency in the design will cost you ten times over, so
you are always seeking to get the tables to maximum efficiency. In Physics
we say you want the "lowest energy state".

As for requirements change, that's a "when" not an "if". Embrace it, love
it, live it. A good programmer codes against errors and changing
requirements, a naive programmer codes for today.

But of course don't take my word for it. Make it a point to try them both
in your travels, and see which one turns out easier on a system that grows
over time.
[color=blue]
>[color=green]
>> 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.[/color]
>
> 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.[/color]

Abstraction does require a lot more work. That's why so few programmers
actually do it. It only pays when you are taking on a job (or jobs) large
enough to tip the scales.

[color=blue]
>
> 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.
>[color=green]
>> 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 developmen