Harsh Iyer

2023BCS0199 Batch -1

Hospital Management System - Schema

Entities

Entity wise analysis

Patient ID Name Contact Disease Date of Birth
Doctor ID Name Contact Education Date of Birth Speciality
Batch ID Equipment Medicine Quantity
Room Number Room Room Description
Record UID Admission Date Bills Paperwork Doctor Records Joining Date
Staff ID Name Contact Salary Date of Birth Joining Date Job Description

Entity Wise Schema analysis

Here we can see that every doctor has a unique primary key called Doctor ID, and several attributes assigned to the Doctor entity.
Hence, we assign the Doctor entity its own unique table with the below schema.
Doctor ( 𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳̲ , Name , Contact , Speciality , Education , Salary , DOB )

The patients are all assigned a unique Patient ID which acts as the primary key in this entity scope. The patient table is also assigned other attributes like Name, Contact, Disease, DOB and has several relations with other entities like Records, Doctors, Infrastructure.
The patient schema will look a little like the one shown below.
Patient ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳͢ , Name , Contact , Disease , DOB )

Records are basically the paperwork storage for the Hospital, which consists of many attributes like Bills, Paperwork, Patient Medical History, Doctor History and etc.
It’s schema will look a bit like this.
Records ( 𝚁̲𝚎̲𝚌̲𝚘̲𝚛̲𝚍̲ ̲𝙸̲𝙳͢ , Record Type , Record Data , Creation Date , Patient History , Doctor History )

Other Staff consist of all non core medical staff like workers, etc.
They all have their own unique attributes, are assigned a Staff ID which acts as the primary key in this scope, and have other attributes like Name, Contact, Salary, DOB, Description, etc.
It’s schema will look a bit like this.
Other_Staff ( 𝚂̲𝚝̲𝚊̲𝚏̲𝚏̲ ̲𝙸̲𝙳̲ , Name , Contact , Salary , DOB , Staff Type , Description )

This talks about the Hospital’s infrastructure, mainly the rooms and their allotment status and availability.
Its schema will look like this:
Infrastructure ( 𝚁̲𝚘̲𝚘̲𝚖̲ ̲𝙽̲𝚞̲𝚖̲𝚋̲𝚎̲𝚛̲ , Room Description , Allotment Status )

Discussing the Relations between the Entities

Here, Patient and Doctor are related by the relation Appointment, which is a Many to Many relation, since many patients can be assigned to the same doctor, and one single patient can be assigned to multiple doctors of varying specialities.
Lets consider the schema for this relation between Doctor to Patient, which will require 3 tables.
Doctor ( 𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳̲ , Name , Contact , Speciality , Education , Salary , DOB )

Patient ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳͢ , Name , Contact , Disease , DOB )

Appointment ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳̲ ̲,̲ ̲𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳͢ )

Here, the Appointment relation consists of 2 foreign keys, Patient ID & Doctor ID which collectively form the Primary key for the relational table Appointment.

In this Relation between the Doctor and the Room allotted to them, this relation is of type ONE to ONE, since one doctor can be assigned only one room at max.
To form the schema for this relation, we can make 2 tables and add the Primary key of one table to the other table as a Foreign key. I will add the Room Number, which is the Primary key of the Entity Infrastructure, as a Foreign Key for the Entity Doctor.

This is how it should look like:

Doctor ( 𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳̲ , Name , Contact , Speciality , Education , Salary , DOB, Room Number )

This is another example for a ONE to ONE relation between a Doctor and his Bank account. Since one doctor can have only one account.
So what I will do is add the primary key of the Accounts entity (Account number) as a foreign key to the Doctor Entity.
The schema should look lke this
Doctor ( 𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳̲ , Name , Contact , Speciality , Education , Salary , DOB, Room Number , Account Number)

Same is for Patient <–Bills–>Accounts and Other Staff <–Salary-> Accounts

The schema for both will look like this:

Patient ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳͢ , Name , Contact , Disease , DOB , Account Number )

Other_Staff ( 𝚂̲𝚝̲𝚊̲𝚏̲𝚏̲ ̲𝙸̲𝙳̲ , Name , Contact , Salary , DOB , Staff Type , Description , Account Number )

Upon admission, all further paperwork on a patient is stored in the Records entity, which consists of a unique Record ID as its primary key, and multiple other attributes as discussed earlier.
This is an example of a ONE to MANY relation, since ONE patient can have MANY records under his/her name.
The updated Schema for the Records Entity will look like:
Records ( 𝚁̲𝚎̲𝚌̲𝚘̲𝚛̲𝚍̲ ̲𝙸̲𝙳͢ , Record Type , Record Data , Creation Date , Patient History , Doctor History , Patient ID )

Same is for Doctor<–Employment–>Records and Other Staff <–Employment–> Accounts

Hence, the updated Records entity will look like;
``

Records ( 𝚁̲𝚎̲𝚌̲𝚘̲𝚛̲𝚍̲ ̲𝙸̲𝙳͢ , Record Type , Record Data , Creation Date , Patient History , Doctor History , Patient ID , Doctor ID , Staff ID)
``

Here, Patient ID, Doctor ID and Staff ID are all foreign keys for the Entity Records, and are assigned values depending on whoever the record belongs to.

Lets talk about the Multi - Valued attributes.

As we have seen, multiple Entities have multi valued attributes assigned to them, like Patient–>Contact, Doctor–> Contact, Records–> Bills, Records–> Paperwork, Other Staff–> Contact to name a few.

To tackle multi valued attributes in schema, we have to make a new table with the primary key from the original entity acting as the foreign key and the multi valued attribute itself acting as the primary key for the new table.

It will look like this for the example Patient–> Contact.

Patient ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳͢ , Name , Contact , Disease , DOB , Account Number )

Here, Contact is a multi value attribute, so we remove Contact from the Patient entity and make a new Contact entity which will look like the one below.

Contact ( Patient ID , 𝙲̲𝚘̲𝚗̲𝚝̲𝚊̲𝚌̲𝚝̲ )

And, the patient entity will look like:
Patient ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳͢ , Name , Disease , DOB , Account Number )

Similarily, we get rid of the multi valued entity contact from the Doctor and Other Staff Entity too.

After updation, they will look like:

Other_Staff ( 𝚂̲𝚝̲𝚊̲𝚏̲𝚏̲ ̲𝙸̲𝙳̲ , Name , Salary , DOB , Staff Type , Description , Account Number )

Doctor ( 𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳̲ , Name , Speciality , Education , Salary , DOB, Room Number , Account Number)

Now, we could make separate tables for the contact of Doctors and Other Staff individually, but instead lets club the contact details for Patients, Doctors and Other Staff together into one single table and add extra columns for each foreign keys.

So, updated contact Entity will look like:
Contact ( 𝙲̲𝚘̲𝚗̲𝚝̲𝚊̲𝚌̲𝚝̲ , Patient ID , Doctor ID , Staff ID)

For example, lets populate this Entity:

Contact Patient ID Doctor ID Staff ID
1234567890 PAT19420 - -
1122334455 - - STA19420
9876543210 - DOC19420 -

Conclusion.

After all this analysis and understanding, below is the updated schema for all Entities, both pre-existing and the new ones we have made.

Doctor ( 𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳̲ , Name , Speciality , Education , Salary , DOB, Room Number , Account Number)

Patient ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳͢ , Name , Disease , DOB , Account Number )

Appointment ( 𝙿̲𝚊̲𝚝̲𝚒̲𝚎̲𝚗̲𝚝̲ ̲𝙸̲𝙳̲ ̲,̲ ̲𝙳̲𝚘̲𝚌̲𝚝̲𝚘̲𝚛̲ ̲𝙸̲𝙳͢ )

Other_Staff ( 𝚂̲𝚝̲𝚊̲𝚏̲𝚏̲ ̲𝙸̲𝙳̲ , Name , Salary , DOB , Staff Type , Description , Account Number )

Contact ( 𝙲̲𝚘̲𝚗̲𝚝̲𝚊̲𝚌̲𝚝̲ , Patient ID , Doctor ID , Staff ID)

Records ( 𝚁̲𝚎̲𝚌̲𝚘̲𝚛̲𝚍̲ ̲𝙸̲𝙳͢ , Record Type , Record Data , Creation Date , Patient History , Doctor History , Patient ID , Doctor ID , Staff ID)

Infrastructure ( 𝚁̲𝚘̲𝚘̲𝚖̲ ̲𝙽̲𝚞̲𝚖̲𝚋̲𝚎̲𝚛̲ , Room Description , Allotment Status )

Accounts ( 𝙰̲𝚌̲𝚌̲𝚘̲𝚞̲𝚗̲𝚝̲ ̲𝙽̲𝚞̲𝚖̲𝚋̲𝚎̲𝚛̲ , Account Owner , Pending Dues )

Inventory ( Batch ID , Type , Expiry , Quantity )

This is also deployed as a website globally here.

Thanks for reading,

Harsh Iyer.