Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Question Triple Composite Key

    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
    Last edited by loconet; 06-29-02 at 22:34.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > 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

  3. #3
    Join Date
    Jun 2002
    Posts
    2
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •