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.
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)
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?