Re: Table Design In General

Chris wrote:
> 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
>

Perhaps add an extra field? VALID_UNTIL (DATE) would do the trick for rights that exclusively change over time; else you'll need an extra table, opening the possibility to have different companies, with different part#/barcodes all pointing to the very same recording (Elvis/Jailhouse Rock/Radio Recorders, Hollywood, April 30, 1957).

All this should come as a result of some Entity-Relationship modeling. I believe you already have some valid pointers in that area.

Can you explain to me how the above recording hooks up to: RCA 47-7035 (45) 20-7035 (78)?
RCA would be the record manufacturer (RCA Victor/His masters Voice) 47-2035(45) could be the song (A-side), 20-7036(78) the B-side (which - for all you wondering historians out there, was Treat Me Nice)

-- 
Regards,
Frank van Bortel