Description

A database design for a real world hospital management system. It consists of 10 schemas/dbs which amount to a total of 77 tables, each pertaining to a different aspect of the hospital.

Schema

Details

1. Room: Consists of office rooms, emergency rooms, doctor offices, labs etc.
2. Consumable: Consists of pharmaceuticals, medical supplies etc.
3. Inventory: This includes from the medical equipment for operations, to lab machines and vehicles.
4. Budget: The expenses and cashflow for each department and daily operations.
5. Management: All personnel of the facility who oversee various aspects.
6. Staff: All worker category personnel.
7. Profile: Human entity document.
8. Patient: Patient profiles.
9. Schedule: Self Explanatory. Includes appointments and equipemnt check-in/check-out.
10. Document: Details that should be on an ouput page.

Project

The folder hierarchy is as follows:

├───design
│   ├───docs
│   ├───queries
│   │   ├───mysql
│   │   └───postgresql
│   ├───schema
│   └───templates
├───db
│   ├───mysql
│   ├───postgresql
└───utils

Notes

Workflow

1. The schema and tables are implemented in /design/templates/schema.yaml.
2. The /utils/yamlToJson.py is used to convert /design/templates/schema.yaml to json /design/templates/schema.json for ease of Editing data types.
3. The /utils/jsonToXsql.py is used to convert the /design/templates/schema_Xsql.json files into /design/queries/X/query_create_xyz.sql queries to generate the database.
4. Finally edit /design/queries/X/query_create_relations.sql query files to properly align foreign keys with schemas. (X = My or P)