PDA

View Full Version : Triple Composite Key

loconet
06-29-02, 14:45
I'm just getting by feet wet in real world examples on DB design and I've come across this probably basic problem that I'd like some advise from you guys....


i have an online magazine as a product that can have:

- subscriptionLengthID (autonumber) (ie: monthly, bi monthly, yearly, etc)

- a licenseID (autonumber) (how many users can access the online magazine for one subscription ie: Individual: 1 user, Commercial: 20-30 users, etc)

- magazineFamilyTypeID: (autonumber) (ie: a classic normal, a feature magazine, a special edition, etc)


In addition... there is a price field for the product which is decided by the magazine author at the moment it is released, which is pretty much content dependant & dependant on all of the other 3 ids.

The way i have it set up so far is that all of this information is on a detailed_product table ..

detailed_product (subscriptionLengthID,magazineFamilyTypeID,
LicenseID,
price,
releaseDate)


Is there a *clean* way to use that composite key as a foreign key on a different table without having to use the 3 numbers every time? I was thinking something like an auto_number representing the combination (a Surrogate key) , but that breaks the proper normalization correct?

Another idea i tried to implement was that of having the key maded up of the 3 other
something like: id1-id2-id3 ., but that seems to stop me from having a *theorical* unlimited number of entries on the detailed_product table :(

Any suggestions or other resources I should look up/read would be of great help

Thanks in advance
r937
06-29-02, 19:53
> detailed_product
> ( subscriptionLengthID
> , magazineFamilyTypeID
> , magazineFamilyTypeID
> , price
> , releaseDate )

i think you meant to have licenseID as the 2nd column -- prolly just a typo on your part

the problem with the above 3-part pk is that you can only have *one* monthly, individual, feature product!!

each of those three should be a foreign key to its respective table, but you do need a different primary key for detailed_product

> Is there a *clean* way to use that composite key
> as a foreign key on a different table ...

i guess that depends on what you mean by "clean" <grin>

> ... without having to use the 3 numbers every time?

well, since they are not a candidate key, i.e. they are not suitable for the primary key of product_detail, you need something else anyway

make productID an autonumber, primary key for product_detail

see also http://rudy.ca/20020620.cfm


rudy
loconet
06-29-02, 23:48
i think you meant to have licenseID as the 2nd column -- prolly just a typo on your part

yes, that was a typo, thank you .. i Fixed it :)


the problem with the above 3-part pk is that you can only have *one* monthly, individual, feature product!!


Very true!


well, since they are not a candidate key, i.e. they are not suitable for the primary key of product_detail, you need something else anyway

make productID an autonumber, primary key for product_detail


product_detail(productID,
subscriptionLengthID,
magazineFamilyTypeID,
magazineFamilyTypeID,
price,
releaseDate)

wouldnt this setup violate normalization rules?

because it looks like price is being based on the productID only ..when it should also be based on the other 3 ids as well.

hmm Now what if we have....

product_detail(productID,
subscriptionLengthID,
magazineFamilyTypeID,
magazineFamilyTypeID,
price,
releaseDate)

and then just use the productID (only) as a foreign on its respective tables. Is this even legal ? (_proper_?)

Thx for your help
r937
06-30-02, 10:12
product_detail(productID,
subscriptionLengthID,
licenseID,
magazineFamilyTypeID,
price,
releaseDate)

wouldnt this setup violate normalization rules?

because it looks like price is being based on the productID only ..when it should also be based on the other 3 ids as well.

no, that's actually okay

think of it this way -- suppose there were a table for people, with some kind of personID, and foreign keys to parents, environment, etc.... your IQ is "based on" your personID in relational database terms, never mind that it is "based on" your parents, environment, etc.

i know that must be confusing

use productID as the pk in product_detail, and as a foreign key in any tables related to product_detail

rudy