databaserelational-databaseentity-relationshipone-to-one

How can I get partial participation of both entities in 1:1 relationship


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.


Solution

  • Edited

    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:

    1. transfer P.K of 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.
    2. to avoid Nullification, make another table 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.

    1. same as option 1 in c1.
    2. same as option 2 in c1. But you should make another table (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.