Denormalizing Dillema!

[1]

I am in a structural dillema.
I have several one-many relationships, one side is a products table.
many side tables are: barcodes, specifications, measurements, etc

The reason for having all these is that each has a different set of fields,
and each table is used for different

things. forms, reports, etc..

The catch here is that is a user wants to add another 'category', such as
recipes, he doesn't know in what table

to store the data. On the other hand, it is way to convenient for me to
perform label printing and check digit

calculations on the barcodes table, comparisons of measured values in
measurements etc.

The question is: Is it better to keep it that way and add another table for
user's "generic product information"

OR denormalize these tables and gather them up in one and use queries,
validation in forms and CAST (or any

other data type transformation) to perform the previously mentioned
actions??

PS. In the denormalizing scenario some fields will be truncuted to allow a
field to hold eg. barcode in one

record and product size in another.

Any help would be mostly appreciated.

Thanx in Advance.

 

[John Vinson]

I am in a structural dillema.
I have several one-many relationships, one side is a products table.
many side tables are: barcodes, specifications, measurements, etc

Does each Product in fact have many Barcodes? I can see *many to many*
relationships to Specifications - i.e. there is a list of
specifications which each product must meet, and another table listing
how each product meets a particular specification...

The reason for having all these is that each has a different set of fields,
and each table is used for different

things. forms, reports, etc..

If you're assuming that all the data must be in one table in order to
generate a Report on that data, think again. It's perfectly routine to
base reports on a selective Query!

The catch here is that is a user wants to add another 'category', such as
recipes, he doesn't know in what table

The user should not be messing with table design... period... unless
the user is the table developer. This applies to adding new tables,
and even more so to adding new fields! The user should work with you
to add a new table, or new fields, as appropriate for the business
need.

to store the data. On the other hand, it is way to convenient for me to
perform label printing and check digit
calculations on the barcodes table, comparisons of measured values in
measurements etc.

The question is: Is it better to keep it that way and add another table for
user's "generic product information"
OR denormalize these tables and gather them up in one and use queries,
validation in forms and CAST (or any
other data type transformation) to perform the previously mentioned
actions??

PS. In the denormalizing scenario some fields will be truncuted to allow a
field to hold eg. barcode in one
record and product size in another.

That would be a Very Bad Idea Indeed! <g>

Denormalization - done carefully, judiciously, and with great
reluctance - is sometimes useful. But it's much better to thoroughly
identify all of the Entities and their relationships up front, build a
normalized data structure to handle them, and add to that data
structure in a normalized, modular way when new requirements arise.

John W. Vinson[MVP]

 

[1]

Does each Product in fact have many Barcodes? I can see *many to many*

relationships to Specifications - i.e. there is a list of
specifications which each product must meet, and another table listing
how each product meets a particular specification...

Each product spans among several packings, each bearing its own barcode.

If you're assuming that all the data must be in one table in order to
generate a Report on that data, think again. It's perfectly routine to
base reports on a selective Query!

No doubt about that.

The user should not be messing with table design... period... unless
the user is the table developer. This applies to adding new tables,
and even more so to adding new fields! The user should work with you
to add a new table, or new fields, as appropriate for the business
need.

I don't mean messing with table design. I mean that if a user wants to store
recipe information in the database, and a table (form, report, etc) not
specifically designed for that exists, he will have to use a table with
'similar' format to store that info in. To make it simplier:
The specifications table has four fields: ID, SpecCategory (that looks up
from SpecCat table), Product (looks up from Prod Table), SpecValue (holding
the value)
eg.
ID SpecCategory Product SpecValue
1 netweight Feta Cheese 18 (kg)

Should he want to insert recipe information, with a little bit of
imagination he would add a 'recipe' category in the corresponding table and
the specs table would be something like:
ID SpecCategory Product SpecValue
2 recipe feta cheese goat's milk, culture (etc)

But that would be only for informational purposes because specs table was
not designed to handle recipes.

On the other hand, If I make eg. a recipes table: ID, Product(lookup),
Ingredient, quantity
What happens if someone wants to have a Loss of Weight field (which I would
have not imagined was needed).

Surpassing that, completely normalizing all the tables is giving me a
headache because the tables that need maintenance multiply!

 

[John Vinson]

Surpassing that, completely normalizing all the tables is giving me a
headache because the tables that need maintenance multiply!

Well... all I can say is, that's why database administrators get
paid...

Putting data into a table which was designed for a different kind of
data is NOT a good idea; creating a table for undefined, freeform data
is possible but a much worse administrative headache in the long run
("what does THIS mean? Who put it into this table and why?").

It's just a reality that a properly designed relational database
system needs a table for each business Entity that needs to be
represented.

John W. Vinson[MVP]

 

[Bill Edwards]

If you have to ask "Do I denormalize a table" then the answer is NO.