Re: Table Design In General

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:d6hfef$klp$1_at_news6.zwoll1.ov.home.nl...
> Chris wrote:
>> Frank,
>>
>> While I can definitely appreciate the humor in your post, perhaps you
>> are not familiar with the recording industry ...
>>
>> Let me most assuredly inform you that in this industry part numbers can
>> (and do) change all the time. For example:
>>
>> Record label A manufactures and distributes Album X from artist Y -
>> part number is 123 and UPC is 789.
>>
>> Record label B acquires/buys/whatever record company A. Album X by
>> artist Y is now part number 456 from company B with the same 789 UPC
>> code (unitl they run out of inventory, at which time the UPC will
>> change to reflect the new vendor if they reprint the title).
>>
>> Again - this is just one tiny illustration of an EXTREMELY common
>> event.
>>
>> I may not be a DBA, but I certainly know my business.
>>
>> Thanks,
>> Chris
>>
>
> You just solved your problem: the primary key
> did not change:
> Company - part# - UPC #
>
> In other words: My Elvis album is still Elvis,
> and my Deutsche Grammophon Beethoven s 9th by
> the Berliner Philharmoniker, conducted by Herbert von Karajan
> is still that, and will always be.
> --
> Regards,
> Frank van Bortel

Chris,

This is where good Entity/Relational analysis comes into play, and good Entity definitions.

What Frank is calling an album is quite different from what you are calling an album -- which is a typcial problem during data modelling excercises.

Your album is somthing like a 'final mix' or 'master recording'. It is a 'thing' (entity)that is related to an Artist (another entity), and in all likelihood is identified, in part, by the artist (that is, without the artist, it cannot exist, and the artist can never be changed once established -- so a specific Nat King Cole recording is always Nat's recording, but when Natalie is mixed in, it's not the same recording) So a potential PK for the recording could well be ArtistID + RecordingNo, with ArtistID referencing the Artist entity for the primary artist's instance (record). However, since a recording could easily have multiple artists (many-to-many relationship), it may be necessary to just system-assign a RecordingID (and use an intersection entity to model the many-to-many relationship with the Artist entity - which table would include an indicator of the primary artist, if applicable). However, your approach will depend on the curernt and future scopy of your system.

Since your 'album' (let's call it Recording) can be owned by more than one Company, there must be another intersection entity (also called associative table once implemented in the database) -- which models all Recordings owned by a Company, and all Companies that own a Recording.

Now, you bring your Vendor into the mix (meaning this situation, not the mix done at recording time), and you're getting closer to Frank's 'album' -- but not yet. The Vendor probably has multiple production runs (depending if the UPC number change only by Vendor or if the Vendor changes it for each production run -- would that be what was called a 'pressing' back in the days of vinyl?). Add to that different media, and after another few tables we finally get to Frank's 'album' -- his Elvis 8-track produced in 1974 by Sunset Bootleggers, LLC.

You likely will end up with mult-part PKs like Frank's example -- but you have to do the full analysis to understand where the parts come from, whether their values can change and the entity still exist (most typically they should not be changeable). Keep in mind that as you do your analysis, you should retain all these multi-part PK's (with all parts except one participating in an identifiable FK) rather than arbitrarily substituting surrogate keys, as these multi-part PKs convey valuable information about the relationships and how they are formed, as well as the scope of your system. For example, Frank's 3-part PK did not include Vendor, so it may well be that in his design, Vendor is out-of-scope -- if Vendor is in scope, the UPC# may or may not be appropriate, as it is actually an 'intelligent key', containing the Vendor# and Vendor's ItemID (plus a derived checksum) which you may want to model as separate attributes.

Have fun!

++mcs Received on Thu May 19 2005 - 05:45:55 CDT