locked
How to link a composite table to another? RRS feed

  • Question

  • Hi friend,

    In the link http://www.mediafire.com/?mdmyddjddtz

    shows ERD snippet 5 tables:

    tblMediaType - to hold Media types DVD, VHS, Blue Ray

    tblLoanTypes - to holds Loan types such as Overnightly, Weekly, Fortnightly

    tblLendingRates - Holds Lending rates such as a DVD has several rates based on, Overnightly, weekly and fornightly and so forth for VHS and Blue Ray

    tblMovie - Holds Movies

    tblMovieCopies - holds copies of each Movie in tblMovies

    The problme that i have is how do i link tblLendingRates table to the tblMovieCopies table?

    If i consider the cardinality, i could come up with a following logic:

    1. A copy can have serveral LendingRates

    2. A LendingRate can be of Many Copies:  Such as a copy of "Avatar" overnightly rental rate could be equal to acopy of "Hancock"'s fortnihgtly rental rate

    Thereforre i have to set up a another bridging entity that made things bit complicated... coudl any one has any idea on how to simplify this?

    thanks

     

    Monday, January 4, 2010 2:23 PM

Answers


  • The problme that i have is how do i link tblLendingRates table to the tblMovieCopies table?


    It appears the relationship is many-to-many. That can be best represented by a junction table (xref):

    CREATE TABLE MovieCopiesLendingRatesXref
    ( MovieCopiesID int references tblMovieCopies (MovieCopiesID),
      LendingRatesID int references tblLendingRates (LendingRateID),
      ModifiedDate datetime default(getdate()),
    CONSTRAINT pkMCLR PRIMARY KEY ( MovieCopiesID, LendingRatesID));

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Monday, January 11, 2010 8:47 AM
    Friday, January 8, 2010 7:33 AM

All replies

  • Based on what you said, it looks like you need a bridging table between these two tables, and have the primary key from each table as a foreign key in the bridging table. That will give you 2 1-to-Many relationships and you should be able to get data easily.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, January 4, 2010 5:36 PM

  • The problme that i have is how do i link tblLendingRates table to the tblMovieCopies table?


    It appears the relationship is many-to-many. That can be best represented by a junction table (xref):

    CREATE TABLE MovieCopiesLendingRatesXref
    ( MovieCopiesID int references tblMovieCopies (MovieCopiesID),
      LendingRatesID int references tblLendingRates (LendingRateID),
      ModifiedDate datetime default(getdate()),
    CONSTRAINT pkMCLR PRIMARY KEY ( MovieCopiesID, LendingRatesID));

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Monday, January 11, 2010 8:47 AM
    Friday, January 8, 2010 7:33 AM