Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: One Realtionship only

    Please could someone help me with my Relationships!!!

    I think there is something wrong with the Relationship between tblFinance (where all the money transactions in and out are recorded) and tblContract????

    Currently the tbl.Contract.ContractID (PK) is a one-to-many relationship with tblFinance.ContractID (FK)
    A Contract can have more than one FinanceID (so this seems to be OK)?

    But I also think that instead, maybe I should be creating a FinanceID (FK) in tblContract.
    Then should I link tbl.Finance.FinanceID (PK) as a one-to-many relationship with tblContract.FinanceID (FK).

    Or are both options the same thing?
    Attached Files Attached Files
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you want to create a many-to-many relationship between two tables (tblContract and tblFinance in this case), you use a junction table (or bridge table):
    Code:
    CREATE TABLE Tbl_TblFinance_x_TblContract
    (
        PK_TblFinance LONG, 
        PK_TblContract LONG, 
        CONSTRAINT FK_TblFinance FOREIGN KEY (PK_TblFinance) REFERENCES TblFinance, 
        CONSTRAINT FK_TblContract FOREIGN KEY (PK_TblContract) REFERENCES TblContract, 
        CONSTRAINT PK_Tbl_TblFinance_x_TblContract PRIMARY KEY (PK_TblFinance, PK_TblContract)
    );
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Thanks very much for your reply, Sinndho.

    And thanks for the coding, which I know would have taken me weeks or months to compile. I do not know about many-to-many relationships and so now is my chance?

    However, after thinking about this again, I think each unique FinanceID (which may be payment of electric bill, maintenance work etc) would only relate to only one ContractID? So I guess this is a one-to-many relationship.

    So, is it best to add a FinanceID FK to the Contract Table?

    But if I add a "FinanceID foreign key" to the Contract Table, then this "field" in the Contract Table will just remain empty - it will never be populated. I wondered if this was OK and if it is normal? Is the only reason for doing this, so that a relationship can be created between the two tables?
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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