If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Denormalizing Dillema!
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 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. |
#2
|
|||
|
|||
Denormalizing Dillema!
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] |
#3
|
|||
|
|||
Denormalizing Dillema!
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! |
#4
|
|||
|
|||
Denormalizing Dillema!
On Tue, 1 Nov 2005 12:52:00 +0200, "1" 1@1 wrote:
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] |
#5
|
|||
|
|||
Denormalizing Dillema!
If you have to ask "Do I denormalize a table" then the answer is NO.
"1" 1@1 wrote in message ... 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 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SUM dillema | Matt | General Discussion | 1 | August 12th, 2005 05:27 PM |
Newsgroup Dillema | mark4man | Outlook Express | 3 | June 3rd, 2005 06:04 PM |