CODEWITHSUNDEEP
mysql
Anni
Anni

Reputation: 403

Database: 1:1 relationship

In this lecture they mentioned 1:1 relationship.

One can represent 1:1 relationship as follows: one lecturer teaches only one subject. It can be done either by adding a subject_id to a lecturer table or by adding a lecturer_id into a subject table.

enter image description here

I have some doubts regarding either the explanation or my understanding.

Lets take the first example with adding a subject_id to a lecturer table.

LECTURE_ID LECTURE_NAME SUBJECT_ID
4          Kolmogorov   6
5          Schmidt      6

So, in fact two lecturers can teach one subject and it is not a 1:1 relation.

Lets take the second example with adding a lecturer_id to a subject table.

SUBJECT_ID SUBJECTE_NAME LECTURER_ID
5          Math          1
6          Arts          1

One lecturer teaches two different subjects.

Is there something I am missing? Or does the foreign key have to appear only once in the table? Because on this page the foreign key (P_id) appeas twice in the "Order" table.

Upvotes: 0

Views: 1068

Answers (1)

H0wie12
H0wie12

Reputation: 33

Well, none of these solutions are optimal. But if you have to choose, I will say that the LECTURER_ID should be a foreign key in the SUBJECT table.

But i would recommend you yo use the Boyce Codd normal form. You would then create three tables in total.

LECTURER

LECTURE_ID(PK) LECTURE_NAME
4              Kolmogorov
5              Schmidt

SUBJECT

SUBJECT_ID(PK) SUBJECTE_NAME
5              Math
6              Arts

LECTURER_SUBJECT

LECTURE_ID(PK)(FK) SUBJECT_ID (PK)(FK)
4                  5
4                  6

This way, a lecturer can have multiple subjects, and a subject can have multiple lecturers.

Upvotes: 1

Related Questions