Re: Table Design In General

Frank - I see where you are coming from with this, but it is problematic.

How would I then go about reporting total sales, for instance?

If my internal ID changes, I'd have no way of linking together the mulitple records that exist for my part, since of course any and/or all of the key values in your example may be changed.

Under your theory, and using my earlier example, I'd have these two parts in my system for Heartbreak Hotel, by the King:

A (Company), 123 (Part#), 789 (Barcode)
B (Company), 456 (Part#), 789 (Barcode)

Now - company B presses new CD's with their own UPC - so this record needs to be added:

B (Company), 456 (Part#), 246 (Barcode)

But now lets suppose that record comany C aquires the rights to this album. I also need to add this record:

C (Company), 135 (Part#), 468 (Barcode)

If I do not use an internal ("dumb") key to represent this item, how can I calculate any sort of historical data referencing Heartbreak Hotel by Elvis Presley. How would I know that in reality these 4 separate products really represent the same Artist/Title/Media?

I know I may be over simplifying, but it is in large part due to my ignorance of DB design. As I said before, I am a programmer not a DBA - this is all new territory for me.

Thanks again for taking the time.

Chris Received on Fri May 20 2005 - 21:59:29 CDT