CODEWITHSUNDEEP
database-design
StackOverflower
StackOverflower

Reputation: 5781

Modeling "optional" foreign key

I have 2 entities

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

Upvotes: 2

Views: 606

Answers (3)

daefu
daefu

Reputation: 471

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.

Upvotes: 2

Adam Robinson
Adam Robinson

Reputation: 185703

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.

Upvotes: 5

lewiguez
lewiguez

Reputation: 3823

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.

Upvotes: 2

Related Questions