Modeling "optional" foreign key

Question

Hi! I have 2 entities

• User
• UserCreditCard

A user can have a credit card or not. How would you model the relation and why?

Option 1: User has a foreign key to UserCreditCard that may be null

Option 2: UserCreditCard has a foreign key to User that can't be null

EDIT

My mistake not remarking that user will have 0 or 1 credit cards, no more

Answer 1

I think you already stated how and why you should model it:

A user can have a credit card or not

Go with option 1. You'll be able to tell who has a card and who doesn't by checking the foreign key and your mappings will still work because if the credit card foreign key is null, the credit card won't match to anything so it'll just be null. You'll have to be careful when you delete the credit card that you delete the mapping in the User entity, however.

Answer 2

I would avoid making a two-way relationship here. It would seem to make more sense to simply model your CreditCard table with a UserID column that references your User table. Adding a CreditCardID reference to User just adds a needless level of complexity and opportunity for error.

I would recommend removing the reference from User to CreditCard and making the reference from CreditCard to User non-nullable and indexed. This should give you everything you need.

Answer 3

Go for option two. It has less complexity and it also models the case where a user has more than one credit card, or changed his credit card.