CODEWITHSUNDEEP
sqlsql-serverdatabase-design
pantonis
pantonis

Reputation: 6467

Supertype & Subtypes and one to one relationship

I have the following supertype/multiple subtypes tables in SQL Server supertype: Doctor and subtypes: Paediatrician, Orthopedic and Dentist

    create table Doctor
(
    DoctorID int primary key,
    Name varchar(100),
    -- add some other common attributes (all of vendor, sponsor, volunteer have) here.
)

create table Paediatrician
(
    PaediatricianId int primary key,
    DoctorID int foreign key references Doctor(DoctorID)
    -- add some other attributes related to Paediatrician here.
)

create table Orthopedic
(
    OrthopedicId int primary key,
    DoctorID int foreign key references Doctor(DoctorID)
    -- add some other attributes related to Orthopedic here.
)

create table Dentist
(
    DentistId int primary key,
    DoctorID int foreign key references Doctor(DoctorID)
    -- add some other attributes related to Dentisthere.
)

My business logic is that a doctor can be either a Paediatrician, Dentist or an Orthopedic. Cannot be more than one of the subtypes. Based on the above design this is not enforced. I can create Doctor with Id = 1 and then go to Dentist and Orthopedictables and assign DoctorId value of 1 in both tables. How do I enforce it so that a doctor can be present at only one table?

Upvotes: 1

Views: 1164

Answers (2)

Dugggie
Dugggie

Reputation: 366

I would arrange this bit differently. I would have 3 tables, a Doctor table (like you already have), a Specialist table and a SpecialistAttributes table.

The Doctor table contains all the Doctors' info, easy.

The Specialist Table contains your SpecialistTypeID and SpecialistDescription etc. Your 3 example specialists would each be a row in this table.

The SpecialistAttributes table contains all the attributes needed for the specialists. In your Doctor table, you have a foreign key to lookup the SpecialistTypeID, so there can be only 1, then the SpecialistType has a number of SpecislaistAttibutes it can link to.

The other benefit of organising your data this way is that of you need to add any specialists roles or attributes, you don't need to change the structure of your database, just add more rows.

Doctor Table
    | ID | Name     | Specialist_FK |
    ---------------------------------
    | 1  | Smith    | 2             |
    | 2  | Davies   | 3             |
    | 3  | Jones    | 3             |

Specialist Table
    | ID | Speciality    |
    ----------------------
    | 1  | Paediatrician |
    | 2  | Orthopedic    |
    | 3  | Dentist       |

SpecialistAttribute Table
    | ID | SpecialityID+FK | Description          | Other      |
    ------------------------------------------------------------
    | 1  | 1               | Paediatrician Info 1 | Other Info |
    | 2  | 1               | Paediatrician Info 2 | Other Info |
    | 3  | 2               | Orthopedic Info 1    | Other Info |
    | 4  | 2               | Orthopedic Info 1    | Other Info |
    | 5  | 3               | Dentist Info 1       | Other Info |
    | 6  | 4               | Dentist Info 1       | Other Info |

Upvotes: 1

Mukesh Arora
Mukesh Arora

Reputation: 1813

There is no inbuild constraints/feature in the SQL server to handle this. You need to write custom login for it. Either in the procedure or Trigger.

You can write a stored procedure which would be responsible to insert in these tables. before insert, it will validate that if doctor id already exists in any of the tables if yes then an error will be custom raised otherwise procedure will insert the record in the respective table.

Upvotes: 0

Related Questions