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

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?