How are relationships built in a database?

O

olegdev2019-10-29 08:30:58

Database design

olegdev, 2019-10-29 08:30:58

How are tables linked in a database? For example, how can I link the tables teachers and subjects. Is it possible to link teacher table by id_subject? And what will it look like?
ps you can specify a source where all this is well described from scratch.

Reply

Answer the question

In order to leave comments, you need to log in

4 answer(s)

S

Sergey Gornostaev, 2019-10-29
@legz

Martin Graber - SQL for mere mortals

A

Anton R., 2019-10-29
@anton_reut

It will look like this:

K

Konstantin Tsvetkov, 2019-10-29
@tsklab

For example, how can I link the tables teachers and subjects. Is it possible to link teacher table by id_subject?

No. Since the teacher can lead several subjects, and the subject of several teachers. That is, a separate table is needed учитель, предмет.

R

Rsa97, 2019-10-29
@Rsa97

Relationships between tables are more of an architectural concept than a technical one. At the database level, they may not be controlled at all or simply give a restriction to maintain the correctness of the database.
A one-to-one relationship is rarely used, and two tables with such a relationship can be replaced with one containing data from both tables.
The one-to-many relationship is most often used when working with all sorts of directories. For example, a table of cities and a table of addresses. The same city can be included in an arbitrary number of addresses, with each address corresponding to only one city. Such a relationship is implemented by specifying the row identifier from the first table in one of the fields of the second table. At the database level, a restriction can be imposed to prevent writing a row with an invalid or unspecified city ID into the address table.
Many-to-many relationship. For example, a user table and a chat table. An arbitrary number of users can participate in one chat, one user can participate in an arbitrary number of chats. This relationship is implemented using an intermediate table, each record of which links one user to one chat. In addition to the relationship, such a table may contain additional data, such as the role of this user in this chat. At the database level, a restriction may be imposed on the creation of records with non-existent or not specified user or chat IDs.