A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Denormalizing Dillema!



 
 
Thread Tools Display Modes
  #1  
Old October 31st, 2005, 05:24 PM
1
external usenet poster
 
Posts: n/a
Default 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  
Old October 31st, 2005, 10:09 PM
John Vinson
external usenet poster
 
Posts: n/a
Default 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  
Old November 1st, 2005, 11:52 AM
1
external usenet poster
 
Posts: n/a
Default 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  
Old November 1st, 2005, 05:37 PM
John Vinson
external usenet poster
 
Posts: n/a
Default 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  
Old November 2nd, 2005, 06:03 AM
Bill Edwards
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 01:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.