"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> wrote

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

"Chris" <ctaliercio (AT) yahoo (DOT) com> wrote:


In some ways your questions can only be answered by years of
experience. But, for your enjoyment, a potted version of what you need
to know is here - I agree with most of what the author says.

--------------------------------------------------
Database Design for Programmers
Most programmers are not DBA's, but are often thrust into the role of
one. This article is a crash course in being and should help you avoid
common mistakes. Full Article
--------------------------------------------------

URL of full article

http://www.hower.org/Kudzu/Articles/DBDesign/index.iwp


You are young, glasshoppah, but you will learn...



Paul...


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

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

Thanks Paul - this article really does capture exactly the situation I
find myself in right now.

Very good foundation for where I need to be heading.

Thanks,
Chris

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

Thanks for the input Frank!

I ran your question by the purchasers here this morning.

The product numbers you have here don't line up with RCA's current
catalog. Product 7035 is now actually Hall & Oates Live At the Apollo.
Their numbering system has changed a bit too: the current available
products are RCA7035.2 (CD) and RCA7035.4 (Cassette).

That being said, the purchasers are pretty sure that the numbering that
you have here indicates a 7" vinyl single (the 45rpm disc) vs. a 12"
vinyl single (the 78rpm disc).

Thanks again,
Chris

Chris wrote:
However, I never say any "hitsingles" on 12" 78rpm; though we did
have a 33/45/78 rpm turntable in those (end 50's) days.

RCA 47-7035 of course is not the same as RCA7035.2, so it may
well point to another recording/artist, etc.

However, it does indicate a problem: RCA should (probably) still
be able to decode RCA 47-7035 into Elvis/Jailhouse ROck/etc.

That's where "active until" (or "active_since") column come in.
Back to the problem at hand: the *key* does not change, maybe
the current description does. But it should (probably - this
would be a business requirement, if at all) be possible to
indicate the properties of RCA 47-7035 for eternity.
--
Regards,
Frank van Bortel