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

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

Tim Van Wassenhove

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

Steve

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*******@attglobal.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

Richard Levasseur

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.

May 14 '06 #11

Kenneth Downs wrote:

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.

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

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.

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.

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

May 15 '06 #12

Richard Levasseur wrote:

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

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

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

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

May 15 '06 #13

Kenneth Downs wrote:

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.

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

May 15 '06 #14

Jerry Stuckle wrote:

Kenneth Downs wrote:
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.

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.

Sorry to hear that.

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.

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)

May 15 '06 #15

Alan Little

Carved in mystic runes upon the very living rock, the last words of
Colin McKinnon of comp.lang.php make plain:

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.

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.

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/

May 15 '06 #16

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.

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)

May 15 '06 #17

Kenneth Downs wrote:

Jerry Stuckle wrote:

Kenneth Downs wrote:
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.

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.

Sorry to hear that.

My customers aren't. It's because the databases were designed properly in the
first place.

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.

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

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

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

May 15 '06 #18

Jerry Stuckle wrote:

Kenneth Downs wrote:
Jerry Stuckle wrote:

Kenneth Downs wrote:
[snip]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.

Sorry to hear that.

My customers aren't. It's because the databases were designed properly in
the first place.

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

May 15 '06 #19

Bent Stigsen wrote:

Jerry Stuckle wrote:

My customers aren't. It's because the databases were designed properly in
the first place.

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,

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

May 15 '06 #20

Richard Levasseur

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*******@attglobal.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*******@attglobal.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*******@attglobal.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

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.

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

May 16 '06 #30

Matt Silberstein

On 14 May 2006 03:33:01 -0700, in comp.lang.php , "windandwaves"
<nf*******@gmail.com> in
<11**********************@j73g2000cwa.googlegroups .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 #31

Bent Stigsen wrote:

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.

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

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

May 16 '06 #32

Bent Stigsen wrote:

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.

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.

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.

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.

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

Stop. If you don't accept that point, you aren't going to get the rest of
it.

A normalized database is the foundation for the least expensive project to
code and maintain. There are two diversions from this ideal. One is
under-normalization, where there are two many columns stacked into tables
that ought to be broken out into their own child tables. This makes some
queries easier at the expense of producing wrong data. The other direction
is to "over-normalize", creating false abstractions that take their toll in
query complexity and the complexity of a framework that must reproduce what
has been thrown away.

This basic truth, that the most efficient system overall is based on a
normalized design, does not seem to come through to people until they've
tried it.

But once you understand this, you realize that you don't want to avoid table
design changes, you simply want to be able to do them efficiently.

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's the E-A-V error stated in its most precise form. You throw away the
ability to use the most basic SQL because you are re-implementing a
relational system.
That is
not cheating or making unnecessary abstractions, it is just plain database
design.
Actually it is database server design, like what the authors of mySQL worry
about. Database design is all about information stored on behalf of the
user, database server design is all about figuring out how to store that
information.

When you confuse a meta-design for a design, you throw away what the authors
of your db server have done, and you end up having to reproduce it. Hows
that for saving a "hell of a lot of work!"
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,

....which is why I have said several times in this thread that you need tools
and methodologies that allow you to change your tables, because in any
living and growing system that is a natural requirement.

Trying to avoid table structure changes is liking trying to find a perpetual
motion machine. The search for an unattainable goal prevents you from
reaching the attainable goals.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

May 17 '06 #33

mail2riyadh

Hi Tia

As you are saying, associated characteristics of a item should be eight
all time. I think this is not a good idea. My experience says that
after few days you will want add more characteristics for an item. So
it would be better making another two table along with Item table

1. Create table with list of characteristics
CharacterID
Associated characteristics
2. Create table to make many to many releationship with Item table and
Characteristics table
ItemID
CharacterID

By the by, this is my personal preference.

Thanks
Riyadh Hossain

May 17 '06 #34

Kenneth Downs wrote:

Bent Stigsen wrote:
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.

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.

Oh come on, the complexity of the problem is not exactly mindboggling.

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.
Sure I have to make a join, if I would want the names as well in the same
select, which I probably would. You got the choice to hard code the names
in the userinterface(s) or have a separate table to select from. If you
hardcode the names, then you probably got the edge in the initial
development of the whole system. Shall we say a couple of minutes perhaps.

As for "hell of a lot of work", that was followed by "should the
requirements change".

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".
I'm sure Codd set a good ideal to aim for, but there is this tiny thing
called the real world. Ideals aren't allways practical when they have to
fit into a whole system, consisting of different parts each with their own
ideal conditions.
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.
Sure, I am betting on the change, that is why I prefer the one solution
before the other, because it saves a hell of a lot of work.
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.
Just answer this question: Given the original scenario of this thread. When
it is requested for another characteristic to be added, which design
requires less (possibly zero) work to adapt to this new requirement.

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.

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.

Some abstractions might take extra work, but there is no physical law that
require it to be so. I can think of situations where it would be more
convenient. Take the present case for instance, where you have a lot of
on/off switches, which most likely would be grouped together in a list to
select from, or as a list of checkboxes. I C# for example, there are
classes that makes it easy to go from; "records in table -> internal
dataset -> listcontrol", because it is a common thing to do. The design
with the abstraction of characteristics fits nicely into this, whereas the
column design doesn't, which means more work for you, less for me.

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

Stop. If you don't accept that point, you aren't going to get the rest of
it.

Perhaps, but it doesn't make me wrong. You would need sticks and stones for
that, and not just throw generalities and hypothetical ideals at me.
A normalized database is the foundation for the least expensive project to
code and maintain. There are two diversions from this ideal. One is
under-normalization, where there are two many columns stacked into tables
that ought to be broken out into their own child tables. This makes some
queries easier at the expense of producing wrong data. The other
direction is to "over-normalize", creating false abstractions that take
their toll in query complexity and the complexity of a framework that must
reproduce what has been thrown away.

This basic truth, that the most efficient system overall is based on a
normalized design, does not seem to come through to people until they've
tried it.

But once you understand this, you realize that you don't want to avoid
table design changes, you simply want to be able to do them efficiently.
So what prevents me for having both? I am a lazy bastard when it comes to
coding, and I'll use any dirty trick I can think of to make my life easier.
I can't really refute that this case isn't a "false abstraction", allthough
I doesn't readily see it as such. Regardless, I'll use it if it suits me,
for the convenience, not for the sake of the abstraction.

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's the E-A-V error stated in its most precise form. You throw away
the ability to use the most basic SQL because you are re-implementing a
relational system.

Bollocks. I have not implied a large scale abstraction of all attributes of
all entities. I just point out that present case could be seen as a shift
in point of view, making the table design quite ordinary and standard
normalization.

That is
not cheating or making unnecessary abstractions, it is just plain
database design.

Actually it is database server design, like what the authors of mySQL
worry
about. Database design is all about information stored on behalf of the
user, database server design is all about figuring out how to store that
information.

When you confuse a meta-design for a design, you throw away what the
authors
of your db server have done, and you end up having to reproduce it. Hows
that for saving a "hell of a lot of work!"

I have allready given you examples of how it would save work, which you
haven't even tried to refute, just snipped it. Use of link tables is hardly
something that gives the authors of MySQL sleepless nights.

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,

...which is why I have said several times in this thread that you need
tools and methodologies that allow you to change your tables, because in
any living and growing system that is a natural requirement.

That is true regardless of the design, and does not prevent one from doing
some tricks in anticipation of future changes, with the purpose of saving
oneself for a hell of a lot of work. Making abstractions without any
calculated reasons would be pointless of course.
Trying to avoid table structure changes is liking trying to find a
perpetual
motion machine. The search for an unattainable goal prevents you from
reaching the attainable goals.

Obviously, one cannot with certainty, avoid changes in the table structure
in a changing environment, but assessing the probability of certain changes
is quite attainable. As Jerry also said, changing just one column in a
table is not necessarily a small thing. You valuate the consequences of
certain events of high probability, and weighed it against the efforts
needed to make a flexible system that can accommodate it. If it is a
reasonable trade, then common sense is to do the trade, regardless if it is
a deviation from a hypothetical ideal. It is the "lowest energy state" if
you like.
/Bent

May 17 '06 #35

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.

Just answer this question: Given the original scenario of this thread.
When it is requested for another characteristic to be added, which design
requires less (possibly zero) work to adapt to this new requirement.

Mine.

Because my tools allow me to change my structures easily, and my framework
adapts accordingly.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

May 17 '06 #36

Kenneth Downs wrote:

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.

Just answer this question: Given the original scenario of this thread.
When it is requested for another characteristic to be added, which design
requires less (possibly zero) work to adapt to this new requirement.

Mine.

Because my tools allow me to change my structures easily, and my framework
adapts accordingly.

Well then lets compare worklist.

Here is mine:
- insert record in table with characteristics.
What does your look like?
/Bent

May 17 '06 #37

Bent Stigsen wrote:

Kenneth Downs wrote:

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.

Just answer this question: Given the original scenario of this thread.
When it is requested for another characteristic to be added, which
design requires less (possibly zero) work to adapt to this new
requirement.

Mine.

Because my tools allow me to change my structures easily, and my
framework adapts accordingly.

Well then lets compare worklist.

Here is mine:
- insert record in table with characteristics.
What does your look like?
/Bent

How much code will you write to support the new value? Any? Do you know?

How will you propagate your changes from test to production? Will your
collaborators know you did this? Do you have only one customer running
this code or several?

How is the new value documented, do you need to document it?

Are there constraints on allowed values? Does the presence of the new value
affect others columns in other tables?

Do any flags exclude other flags? Do any flags require any other flags?

As I've said now three times in the thread, we don't know, we're just
playing hypotheticals. If and only if none of these questions matters,
than go with your cross-reference, because you are talking about user data
then. (In fact by definition you should not then be doing it, they should
be putting it in). But if even one of them matters than you aren't done
yet.

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

May 17 '06 #38

Kenneth Downs wrote:

Bent Stigsen wrote:

Kenneth Downs wrote:

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

Just answer this question: Given the original scenario of this thread.
When it is requested for another characteristic to be added, which
design requires less (possibly zero) work to adapt to this new
requirement.

Mine.

Because my tools allow me to change my structures easily, and my
framework adapts accordingly.
Well then lets compare worklist.

Here is mine:
- insert record in table with characteristics.
What does your look like?
/Bent

How much code will you write to support the new value? Any? Do you know?

That's his (and my) point. None.
How will you propagate your changes from test to production? Will your
collaborators know you did this? Do you have only one customer running
this code or several?

insert record in table with characteristics in production system.
How is the new value documented, do you need to document it?

Self-documenting.
Are there constraints on allowed values? Does the presence of the new value
affect others columns in other tables?

Whatever constraint the customer wants to put on it. And no, it does not affect
other columns in the table.
Do any flags exclude other flags? Do any flags require any other flags?

Nope.
As I've said now three times in the thread, we don't know, we're just
playing hypotheticals. If and only if none of these questions matters,
than go with your cross-reference, because you are talking about user data
then. (In fact by definition you should not then be doing it, they should
be putting it in). But if even one of them matters than you aren't done
yet.

You're adding a new CHARACTERISTIC. Whether or not they have wi-fi facilities
has absolutely nothing to do with the color of their windows. That was in the
original definition of the problem - these are independent characteristics.

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

May 17 '06 #39