Database of Scriptum

In the ER diagram of a Hospital-Based Database System, we can view the entities are Patient, Doctor, Receptionist, Department, Medicine, Test, etc. Among these entities, relationships exist which connect all the entities in the diagram. For example, Patient, Doctor and Receptionist are connected via the relationship Appointment. In other words, a receptionist will set up a doctor’s appointment for a patient. Similarly, Doctor, Patient and Medicine are connected via the relationship Prescription. Here, a doctor may prescribe one or more medicine to a patient. In a similar way, other entities are connected via relationships in a meaningful way.

In this article will be explained about entities or tables that consist in the sub system personnel/ employee of the hospital database.
This sub system maintain some functionalities such as user login, user permission on accessing the system, etc. These tables are created by using DDL syntax in MySql Workbench for MySql database management system. We assume that you are familiar with SQL syntax.

Person
CREATE TABLE Person(
  `personID` varchar(18) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Is_Active` bit(1) DEFAULT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table will store all personnel/ employee of the hospital

Specialists
CREATE TABLE `specialists` (
  `specialistID` varchar(2) NOT NULL,
  `Title` varchar(15) DEFAULT NULL,
  `Specialist` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`specialistID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table is for look up table used by Doctor/ Physician table.

Doctor/ Physician
CREATE TABLE `dokter` (
  `doctorID` varchar(4) NOT NULL,
  `personID` varchar(18) DEFAULT NULL,
  `specialistID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`doctorID`),
  UNIQUE KEY `personID_UNIQUE` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table stores the identity of each doctor, and will be used in another sub system too such as emergency room sub system, ambulatory sub system, inpatient sub system, etc

User.
CREATE TABLE `tuser` (
  `user_id` varchar(20) NOT NULL,
  `encrypted_pass` varbinary(100) DEFAULT NULL,
  `personID` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `personID_UNIQUE` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table stores the user id and password for each person that use the hospital information system entirely. When aplication of each sub system is launched, user will be prompted to input user id and password, if user id and password is match then system will query to table that store every user permition to the system, this table may be "user_modul" or something else like that. Of course this happen at the application level/ user interface level.
May be we need more tables such as Nurse, Officials, etc. It will be presented in next articles.