I've taken a relationship between a Subject
and a Lecturer
as a concrete example. That means that there is a subject
that may be provided by only one lecturer
and a lecturer
that may provide only one subject
. How can I get partial participation of both entities in 1:1 relationship in Relational Data Model (Logical Data Model)? How much tables do I need and how to build them? I'm got stuck.
First Approach: This approach is based on Data Modeling:
Condition 1 (c1): There is a subject
that may be provided by only one lecturer
Condition 2 (c2): and a lecturer
that may provide only one subject
.
To apply c1: you have two options:
lecturer
as F.K to subject
. It can be NULL
too. But if a subject
provided by only one lecturer
, put lecturer
's P.K in it. This option has Nullification.subject_lecturer
(only for using this condition) with two columns (subject_id
and lecturer_id
). They are F.Ks from their original tables (subject
and lecturer
). In this new table you only save a subject that may be provided by only one lecturer
. So you should make subject_id
as UNIQUE. So you can insert only one subject into this table.To apply c2: you have the similar options as c1.
lecturer_subject
) for this. In this new table you should make lecturer_id
as UNIQUE. So you can insert only one lecturer into this table.Can we merge them (subject_lecturer
, lecturer_subject
):
Based on Data Modeling concepts (ER) you have different types of data in c1 and c2.
Second Approach: However, there is a solutions to merge them.
Merge them into one table (subject_lecturer_allinone
) and use a type
column that only can be 0
for c1 records 1
for c2 records.
subject_lecturer_allinone
subject_id is F.K and refers to Subject table
lecturer_id is F.K and refers to lecturer table
type : only can be 0 (for c1 records) and 1 (for c2 records)
We should use 2 UNIQUE constraints to handle coming data.
(lecturer_id , type) when the value of type is 1
(subject_id , type ) when the value of type is 0
This is not ER solution and you should write some Functions or Triggers to handle it.