Conrad Lender <crlender (AT) yahoo (DOT) com> wrote:

It is a mistake that people keep making. We see postings about
it here over and over and over.

Did you know that Russian Roulette has an over 80% success rate?

Would you like to play?

I advise against it and EAV.

And you got them, didn't you?

When you know, EAV is not dubious (doubtful). It is just plain
bad.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Conrad Lender <crlender (AT) yahoo (DOT) com> wrote:

It is a mistake that people keep making. We see postings about
it here over and over and over.

Did you know that Russian Roulette has an over 80% success rate?

Would you like to play?

I advise against it and EAV.

And you got them, didn't you?

When you know, EAV is not dubious (doubtful). It is just plain
bad.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

On 2008-09-25 01:12, Gene Wirchenko wrote:
I can see that a number of people have had bad experiences with EAV
designs, and are vehemently opposed to the whole idea; I understand the
reasons for this. However, I'm still not convinced that our two
"catalog" tables can really be described as EAV.

There was an implicit conclusion that the tables should be refactored,
but no suggestions about how go about it. Allow me to rephrase my question:

1) When you have 63 groups of relatively static categorization options
like I have described (all with the same fields, but belonging to
semantically different categories), would it *really* be advisable to
break that larger table up into 63 separate very small tables with the
same column layout?

2) With the added constraint checks from my previous post, is there any
way that the data/references could possibly become inconsistent?

3) How would you design a database where many of the entities can have
distinct "types" in one or more fields (eg. a "status" field could have
the values "old", "active", "in_review"), and these types need to have
editable textual descriptions? Do you create small
{entityname}_{typename} tables for each of those fields, each containing
only a few rows?


- Conrad

On 2008-09-25 01:12, Gene Wirchenko wrote:
I can see that a number of people have had bad experiences with EAV
designs, and are vehemently opposed to the whole idea; I understand the
reasons for this. However, I'm still not convinced that our two
"catalog" tables can really be described as EAV.

There was an implicit conclusion that the tables should be refactored,
but no suggestions about how go about it. Allow me to rephrase my question:

1) When you have 63 groups of relatively static categorization options
like I have described (all with the same fields, but belonging to
semantically different categories), would it *really* be advisable to
break that larger table up into 63 separate very small tables with the
same column layout?

2) With the added constraint checks from my previous post, is there any
way that the data/references could possibly become inconsistent?

3) How would you design a database where many of the entities can have
distinct "types" in one or more fields (eg. a "status" field could have
the values "old", "active", "in_review"), and these types need to have
editable textual descriptions? Do you create small
{entityname}_{typename} tables for each of those fields, each containing
only a few rows?


- Conrad

On 2008-09-25 01:12, Gene Wirchenko wrote:
I can see that a number of people have had bad experiences with EAV
designs, and are vehemently opposed to the whole idea; I understand the
reasons for this. However, I'm still not convinced that our two
"catalog" tables can really be described as EAV.

There was an implicit conclusion that the tables should be refactored,
but no suggestions about how go about it. Allow me to rephrase my question:

1) When you have 63 groups of relatively static categorization options
like I have described (all with the same fields, but belonging to
semantically different categories), would it *really* be advisable to
break that larger table up into 63 separate very small tables with the
same column layout?

2) With the added constraint checks from my previous post, is there any
way that the data/references could possibly become inconsistent?

3) How would you design a database where many of the entities can have
distinct "types" in one or more fields (eg. a "status" field could have
the values "old", "active", "in_review"), and these types need to have
editable textual descriptions? Do you create small
{entityname}_{typename} tables for each of those fields, each containing
only a few rows?


- Conrad

On Oct 7, 12:20*am, Conrad Lender <crlen... (AT) yahoo (DOT) com> wrote:
Yes. What's wrong with several small tables? You may actually save a
little space (the "type" column likely becomes the new table name so
it is not repeated).

I do not know, but I think it must be possible to get it inconsistent.
Just set a new programmer up to add some new values.
I have used a Descriptions table, but only in applications which had
to be multilingual. If you never configure a translation of your
application, why split the description out?
Do you really want to let users (even power users who should "know
what they are doing") change value descriptions?
I am currently working on a system that has a design similar to yours.
When programming I have to either:
hard code the ID values (very bad style and brittle code, ie, easy to
break)
hard code the descriptions and query for the IDs (less brittle, more
human friendly)

Because of the nested relationships among the codes in my system, I
could not write simple SQL statements to insert a new service. It
required writing a procedure to handle the relationships.

It would be a lot easier to understand and program for a system which
has Status codes in a status table and Service types in a Service
table and so on. Even if those tables have only a few rows. It
actually would make it a lot easier to expand the options.

So I would argue less from the Relational theory view and more from a
practical, how-the-hell-do-I-maintain- this-beast, view.

Take the chance while you have it and split those tables out. You will
be much happier later.

On Oct 7, 12:20*am, Conrad Lender <crlen... (AT) yahoo (DOT) com> wrote:
Yes. What's wrong with several small tables? You may actually save a
little space (the "type" column likely becomes the new table name so
it is not repeated).

I do not know, but I think it must be possible to get it inconsistent.
Just set a new programmer up to add some new values.
I have used a Descriptions table, but only in applications which had
to be multilingual. If you never configure a translation of your
application, why split the description out?
Do you really want to let users (even power users who should "know
what they are doing") change value descriptions?
I am currently working on a system that has a design similar to yours.
When programming I have to either:
hard code the ID values (very bad style and brittle code, ie, easy to
break)
hard code the descriptions and query for the IDs (less brittle, more
human friendly)

Because of the nested relationships among the codes in my system, I
could not write simple SQL statements to insert a new service. It
required writing a procedure to handle the relationships.

It would be a lot easier to understand and program for a system which
has Status codes in a status table and Service types in a Service
table and so on. Even if those tables have only a few rows. It
actually would make it a lot easier to expand the options.

So I would argue less from the Relational theory view and more from a
practical, how-the-hell-do-I-maintain- this-beast, view.

Take the chance while you have it and split those tables out. You will
be much happier later.

On Oct 7, 12:20*am, Conrad Lender <crlen... (AT) yahoo (DOT) com> wrote:
Yes. What's wrong with several small tables? You may actually save a
little space (the "type" column likely becomes the new table name so
it is not repeated).

I do not know, but I think it must be possible to get it inconsistent.
Just set a new programmer up to add some new values.
I have used a Descriptions table, but only in applications which had
to be multilingual. If you never configure a translation of your
application, why split the description out?
Do you really want to let users (even power users who should "know
what they are doing") change value descriptions?
I am currently working on a system that has a design similar to yours.
When programming I have to either:
hard code the ID values (very bad style and brittle code, ie, easy to
break)
hard code the descriptions and query for the IDs (less brittle, more
human friendly)

Because of the nested relationships among the codes in my system, I
could not write simple SQL statements to insert a new service. It
required writing a procedure to handle the relationships.

It would be a lot easier to understand and program for a system which
has Status codes in a status table and Service types in a Service
table and so on. Even if those tables have only a few rows. It
actually would make it a lot easier to expand the options.

So I would argue less from the Relational theory view and more from a
practical, how-the-hell-do-I-maintain- this-beast, view.

Take the chance while you have it and split those tables out. You will
be much happier later.