On Mon, 31 Oct 2005 18:24:21 +0200, "1" 1@1 wrote:

I am in a structural dillema.
I have several one-many relationships, one side is a products table.
many side tables a 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]