A
A
Andrew2019-05-23 04:18:10
Database design
Andrew, 2019-05-23 04:18:10

How to properly link two tables if the attribute of the first table is not always needed in the attribute of the second table?

The example is taken from the head, the real task is different, but similar.
Let's say there is a table of people and cars.

Люди
----
Id
Имя

Машины
----
Id
Марка

You need to link them - the ability to specify the brand of their car for people.
There are two options - to add the CarId field to the people table, or to make an intermediate table.
МашиныУЛюдей
----
CarId
PersonId

Everything at first glance is simple - if there is one car, then the field, if there are several - a table.
But if the car is exclusively one and few people own them? Adding an extra field is impractical (the size will grow, the length of each record will increase), and the intermediate table can have a bad effect on performance. Or is there no difference? And if less than 1% of people have a car? What if it's 10%?
Are there any general practices and recommendations on which is better to choose?

Answer the question

In order to leave comments, you need to log in

3 answer(s)
S
sim3x, 2019-05-23
@sim3x

A car can belong to several people
M2M - many to many - intermediate table
The car belongs to only one and is somehow unique O2O
- one to one - foreign key in the Car table or in the Person table
machines do not differ
O2M - one to many - FK in the table Man
Write table names in the singular
Both solutions do not affect performance in any way
Forbid yourself to think about performance until you have no load and the schema is in 3NF

K
Konstantin Tsvetkov, 2019-05-23
@tsklab

Table "Person" (code, name), table "Machine" (code, number, brand), table "Brand" (code, name), table "Possession" (code, owner, car, exclusive).

D
ddd329, 2019-05-23
@ddd329

Add the PersonId field to the machines table. The connection will be one-to-many, i.e. one person and zero or more cars. If one machine has several owners, then create an intermediate table, and the relationship will already be many to many.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question