Database Schema

Entities

All of the tables/entities have been associated with several relations and are purely based on the E-R Diagram. The attributes of these tables/entities are precisely shown in the table below.

Entities	Attributes
Paycheck	Chk_Num, Salary, Bonus, Pay_Date
Doctor	D_Name, D_Ssn, Gender, Age, Position, Phone, Address, Office, City, Zip
Diagnosis	Diagnosis_Id, Category
Patient	P_Name, P_Ssn, Gender, Age, Position, Phone, Address, Office, City, Zip
Invoice	Invoice_Num, Amount, Invoice_Date, Due_Date
Medicine	MInventory_Id, M_Name, Manufacturer, Price, Quantity, Exp_Date
Insurance	InsCo_Id, InsCo_Name, Category, Phone, Address, City, Zip
Payment	Invoice_Num, PayTrans_Num, Pay_Method, Pay_Status, Paid_Date
CPT	CPT_Id, Category
Prescription	Prescription_Id, Medicine_Quantity

ER-Diagram

Mapping on a Database Schema

Depending on certain functional dependencies of these relations, the mapping can be done by normalizing the relations and then converting the E-R model into a relational model. Here, we have shown it precisely.

Doctor (D_Ssn, D_Name, Gender, Age, Position, Office, Phone, Address, City, Zip)

Paycheck (D_Ssn, Chk_Num, Salary, Bonus, Pay_Date)

Patient (D_Ssn, P_Ssn, P_Name, Gender, Age, Position, Phone, Address, City, Zip)

Insurance (InsCo_Id, InsCo_Name, Category, Phone, Address, City, Zip)

PatientInsurance (P_Ssn, InsCo_Id)

Invoice (P_Ssn, Invoice_Num, CPT_Id, Diagnosis_Id, Prescription_Id, Amount, Invoice_Date, Due_Date)

Payment (Invoice_Num, PayTrans_Num, Pay_Method, Pay_Status, Paid_Date)

CPT ( CPT_Id, Category)

Diagnosis (Diagnosis_Id, Category)

Prescription (Prescription_Id, Med_Quantity)

Medicine (MedInventory_Id, Med_Name, Manufacturer, Price, Quantity, Exp_Date)

PrescriptionMedicine (Prescription_Id, Medinventory_Id)

A Data Dictionary for All Entity Attributes

The data dictionary is as follows.

Name of the Relation	Data Dictionary
Doctor	D_Ssn->D_Name, Gender, Age, Position, Office, Phone, Address, City, Zip
Patient	P_Ssn ->P_Name, Gender, Age, Position, Phone, Address, City, Zip D_Ssn, P_Ssn !P_Name, Gender, Age, Position, Phone, Address, City, Zip
Paycheck	Chk_Num ! Salary, Bonus, Pay_Date .D_Ssn, Chk_Num ! Salary, Bonus, Pay_Date
Insurance	InsCo_Id !InsCo_Name, Category, Phone, Address, City, Zip
Payment	PayTrans_Num ->Pay_Method, Pay_Status, Paid_Date,Invoice_Num, PayTrans_Num ! Pay_Method, Pay_Status, Paid_Date
Diagnosis	Diagnosis_Id ->Category
Prescription	Prescription_Id -> Medicine Quantity
Invoice	Invoice_Num -> Amount, Invoice_Date, Due_Date,P_Ssn, Invoice_Num, CPT_Id, Diagnosis_Id, Prescription_Id ! Amount, Invoice_Date, Due_Date
Medicine	MInventory_Id ->M_Name, Manufacturer, Price, Exp_Date Prescription_Id, MInventory_Id !M_Name, Manufacturer, Price, Exp_Date
CPT	CPT_Id ->Category

Discussion

The one area of this design that looked difficult to develop was the index. I was still able to achieve the desired results by careful design of the database model and by optimizing the queries run by an application on the database. In addition, I imported SQL into the PostgreSQL database, and when I was importing, I kept checking the execution of the previous queries.

While mapping the schema, it was difficult for me to determine the width of the columns. But when I tried to resolve the issue, I got to know that if the field would be plain text in a GUI, then the field could restore up to 1000 characters of the text and then I was able to measure the width of columns.

The first hosting consideration is back-up. It is not possible to ensure security without a stable and consistent backup system. This prevents possible attacks and makes the text or content look better and accessible. Another hosting consideration is analytics. If a web host does not provide this facility, then it is not good to go with. Hostgator and GoDaddy are two of the most famous and affordable hosting providers.

For this particular database, the required resources are several paid software or programs and a hosting account. On the other hand, the estimated budget is $10,000.