Results 1 to 2 of 2

Thread: Sharing a Table

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Sharing a Table

    I have an Address table that can be tied to either a User or a CreditCard. A User can only have one Address. A CreditCard can only have one Address. An Address cannot be shared between a User and a CreditCard.

    Should I:

    1) Put both UserId and CreditCardId into the Address table (ugly!)

    2) Create many2many style tables between both User-Address and CreditCard-Address (seems like a lot of extra joins!)

    3) Create a single many2many table with a Type attribute that can be used to join Address to any table (hmmm)

    4) Something else

    Thanks so much!
    Alan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Sharing a Table

    1) is quite common, sometimes called an "arc" relationship. You would want a check constraint to ensure that only 1 of the 2 foreign keys is populated, if that is a true constraint*.

    2) this allows for many Users and/or Credit Cards per Address, which may be desirable.

    3) no - that makes it impossible to use a foreign key constraint to enforce integrity. But you could have a "many2many" table with a user_id and a credit_card_id, as in (1).

    4) If a user can only have 1 address, and a credit card can only have 1 address, how about having the foreign keys the other way round, i.e. have address_id in the User table and in the Customer table?

    * The constraint that an address can be associated with a User or a Credit Card, but not both, doesn't sound valid to me. What will you do if a new User comes along who happens to have the same address as an existing Credit Card? Come to that, what if 2 users do in fact have the same address?

Posting Permissions

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