Geek Answers Handbook

SQL Server Table Relationship Practice

What is the recommended method in this situation:

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

So, there is a Customer table and a table for cars, the Client can have zero or one car, the car can be associated with many customer tables.

  • Should I add a CarID column with a null value for the client or
  • Should I create a Customer_Car_Map table containing CustomerID and CarID

I ask this because I do not know if it is recommended to use a null foreign key?

+4
source share
5 answers

As long as you are 100% sure that the client will never have more than 1 car, go with the first offer. If you think that there is even a small chance that this could ever spread to many-to-many relationships, go with the second option to save headaches in the future.

+5
source

I would make the first decision, it is much easier. With another solution, you will need a key or unique constraint to ensure that there are no multiple vehicles for each user. If this was later resolved, you will need to make changes to the circuit with any solution, so go for easy.

+1
source

You can add this CarId column to the Customer table only if you are sure that after your decision customers will not have many cars.

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

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

This is a balancing act, on the one hand, the idea of โ€‹โ€‹not having any zeros in the database.

However, if you accept this rule as given in stone, you can get some massive join statements that are difficult to maintain in a string.

If you are sure that it will be one to one, stick to only two tables.

0
source

IMHO mapping depends on the situation that needs to be presented.

For example, if this is a national registry of cars, then one person can have many cars, but one car must have one owner. A good idea for your first decision is when we want to know in which car some person is installed, because in this case it is very difficult to be in two places at the same time. For general cases, the best solution is many-to-many, and I would choose this advantage of this solution, which in the near future one person will be able to have more than one car.

* A must, because it is possible that one car has two owners.

0
source

More articles:


All Articles