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 ..
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
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