Database Design: Do you need a join table?

Question

There is a table of elephants with fields:
id, name, continent (the names are not repeated within one continent)

Link table with a composite key:
id, status_id

The status table of elephants ( each elephant has many statuses, the status is not deleted when it is changed, but is added to the table to save the status history, the statuses are unique, the same status cannot be used by different elephants ):
status_id, status (местонахождение), date

The goal is to select an elephant and view all its statuses / movements, sorted by date.

After reading this material https://habrahabr.ru/post/193380/ I already doubt the join table, one-to-many connection is obtained, since an elephant can have several statuses, but only one donor can have a status. But then the status table will be
id, status (местонахождение), date
and will have to add a unique primary key column apparently for normalization on 1NF?

Example:

 id - name - cont 1 - Слон Ярик - Африка 2 - Слон Шарик - Австралия id - status_id 1 - 1 1 - 2 status_id - status - date 1 - Видели в Южной Африке - 2016/10/15 10:10:00 2 - Убежал к морю - 2016/10/16 12:00:00

@Mike date in datetime format, so it doesn't matter. - Jean-Claude

Mike mike 38.7k one 25 62 · Accepted Answer · 2016-10-31T14:20:13

Yes, you are on the right track. The join table is of course not needed. It is enough to add an elephant id to the status table. And yes, a unique key in the table is needed. And not only because it requires 1NF, but from practicality. If the table does not have a unique key, then, if you wish, something to change in the database (for example, you specified the wrong status and it needs to be corrected) in the update request, which would indicate which same record to change will have to be listed in every single field just in case. And if by mistake two absolutely identical records got into the database and we want to delete one of them, then it would be practically impossible to do this without a unique key. Make a unique key at your discretion; it can be an elephant id-date or, more conveniently, a separate status_id field.

The final structure of the table:

 id-статуса primary key, id-слона, статус, дата

Answer 2 · 2016-10-31T13:58:17

In the table with elephants, add the status_id field and in the table with the status_id status status_id replace with id . That is, if one elephant has one status. If there are a lot of statuses in the "joining" table, add the id; status_id; slon_id; date; fields id; status_id; slon_id; date; id; status_id; slon_id; date;

@Mike Yes, it is possible, but one elephant may have several statuses
@ Jean-Claude please provide an example of data from several elephants and statuses

Database Design: Do you need a join table?

2 answers 2

More articles: