Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    20

    reference relationship between tables

    Have a question on how to connect tables. if I have a CUSTOMER table (with custid as primary key) and a BANK_ACCOUNT table (with accountid as primary key). To connect them together, we have at least two options:

    option 1:
    put accountid into CUSTOMER table as a foreign key

    option 2:
    put custid into BANK_ACCOUNT table as a foreign key

    to me option 2 may be better because it can handle one customer has more than one account case. But how about if that account is a shared account which shared by a hushand and wife, and the husband and wife are separated customer in this system? Then we need another table in between to resolve this?

    What's your opinon? do we have general rules for those cases?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    You're asking the right questions, but your asking the wrong people - we don't know what your business requirements are.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    When an account can be shared, you need to implement a many-to-many relationship. This is done by creating a third table that contains AccountID and CustomerID as a unique composite key.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Just have a separate CUSTOMER_BANK_ACCOUNT table surely.

    Code:
    CUSTOMER_BANK_ACCOUNT : custid, accountid, role
    With role to show who's the primary owner of the account. This handles 1:M, M:1 or M:M between customers and accounts.

    Edit : Sorry blindman - didn't see your reply until too late.

Posting Permissions

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