Database Schema

Tables

department

column name	data type	details
id	integer	not null, primary key
name	string	not null
location	string	not null
created_at	datetime	not null
updated_at	datetime	not null

staff

column name	data type	details
id	integer	not null, primary key
first_name	string	not null
last_name	string	not null
email	string	not null, indexed, unique
username	string	not null, max_length: 40, indexed, unique
password	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• Index on email, unique: true
• Index on username, unique: true

patient

column name	data type	details
id	integer	not null, primary key
first_name	string	not null
last_name	string	not null
address	string	not null
created_at	datetime	not null
updated_at	datetime	not null

reports

column name	data type	details
id	integer	not null, primary key
title	string	not null
content	string	not null
created_at	datetime	not null
updated_at	datetime	not null

images

column name	data type	details
id	integer	not null, primary key
image	string	not null
patient_id	integer	not null, foreign key
staff_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• patient_id references patient Table
• staff_id references staff Table

fees

column name	data type	details
id	integer	not null, primary key
cost	integer	not null
charges	integer	not null
description	string	not null
created_at	datetime	not null
updated_at	datetime	not null

roles

column name	data type	details
id	integer	not null, primary key
name	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

announcements

column name	data type	details
id	integer	not null, primary key
staff_id	integer	not null, foreign key
content	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• staff_id references staff Table

messages

column name	data type	details
id	integer	not null, primary key
sender	integer	not null
receiver	integer	not null
content	string	not null
created_at	datetime	not null
updated_at	datetime	not null

billing

column name	data type	details
id	integer	not null, primary key
patient_id	integer	not null, foreign key
fees	intarray	not null
created_at	datetime	not null
updated_at	datetime	not null

• patient_id references patient Table

departmentstaffs

column name	data type	details
staff_id	integer	not null, foreign key
department_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• staff_id references staffs Table
• department_id references department Table

departmentreports

column name	data type	details
department_ id	integer	not null, foreign key
report_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• department_id references departments Table
• report_id references reports Table

staffreports

column name	data type	details
staff_ id	integer	not null, foreign key
report_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• staff_id references staffs Table
• report_id references reports Table

patientreports

column name	data type	details
patient_ id	integer	not null, foreign key
report_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• patient_id references patients Table
• report_id references reports Table