SQL Server table relationships practice

Question

What is the recommended way in this situation:

Customer ..* <-------------> 0..1 Car

So there is a Customer table and a Car table, Customer can have zero or one Car, the Car can be linked to many Customer tables.

• Should I add a nullable CarID column to Customer or
• Should I create a Customer_Car_Map table containing CustomerID and CarID

I'm asking this because I don't know if having a nullable foreign key is recommended?

Answer 1

I would do the first solution, it is much simpler. With the other solution, you would need a key or unique constraint to ensure no multiple cars per user. If that later became allowed, you would need to make schema changes with either solution, so, go for the easy one.

Answer 2

You can add this column CarId to the Customer table only if you're sure enough that Customers won't have a lot of cars after your decision has been implemented.

You can also have as many cars as you like for any particular Customer if you do it like this:

Car: CarId, CustomerId, MakeId, ModelId, Color, PlateNumber, VIN
Customer: CustomerId, LastName, FirstName, MiddleName

Answer 3

As long as you're 100% sure a customer will never have more than 1 car, go with your first proposal. If you think there's even a slight chance this could ever expand into a many-to-many relationship, go with your second option now to save yourself headaches in the future.

Answer 4

It is a balancing act, on the one hand it idea to not have any nulls in a database.

However if you take that rule as set in stone you can end up with some massive join statements which become hard to maintain down the line.

If your sure its going to be a one to one relationship stick with just the two tables.

Answer 5

IMHO the mapping depend on the situation that should be presented.

For example if this is a national registry of cars, then One person may have many cars but one car should* have one owner. Good representation for your first solution is when we want to know in which car set some person, because in this case is very hard to be in two places at the same time. For general cases better solution is many-to-many and I would pick a this advantage of this solution that in near future one person will be able to have more then one car.

*should because is possible that one car have two owners.