John Davidson

Message:

I want to design a database for a hospital management system
I have in the system: Doctors, Nurses, patients, accountants, department, and booking .....

what is the best practice for design: first option: user_info table contain: ID, name, age, user_type .....

second option: doctors_table contain : ID, name, age ....., patients_table contain : ID, name, age .....,

third option: you can suggest another one

---

My Answer:
The best practice for designing a MySQL database for a hospital management system would be to create separate tables for each entity (Doctors, Nurses, Patients, Accountants, Departments, Bookings) and establish relationships between them using foreign keys.

Here is a suggested database design:

1. Doctors table:
- ID (Primary Key)
- Name
- Age
- Specialty
- Department_ID (Foreign Key referencing Departments table)

2. Nurses table:
- ID (Primary Key)
- Name
- Age
- Department_ID (Foreign Key referencing Departments table)

3. Patients table:
- ID (Primary Key)
- Name
- Age
- Admission_Date
- Discharge_Date
- Doctor_ID (Foreign Key referencing Doctors table)

4. Accountants table:
- ID (Primary Key)
- Name
- Age

5. Departments table:
- ID (Primary Key)
- Name

6. Bookings table:
- ID (Primary Key)
- Patient_ID (Foreign Key referencing Patients table)
- Doctor_ID (Foreign Key referencing Doctors table)
- Nurse_ID (Foreign Key referencing Nurses table)
- Appointment_Date
- Status

By following this design, you can easily query and retrieve information about each entity and their relationships within the hospital management system. It also allows for better data integrity and scalability in the long run.