Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I want to create a database where one user can either be a Doctor or a Patient (but not both). So that, I can get all doctors or patients from doctor or patient table as well as I can search doctor or patient credentials details from user table. I really confused about which way I use. Please suggest me which diagram I use.
Diagram 1:
Diagram 2:
As User, Doctor and Patient have very different attributes best you can do is split into 3 tables. As User is the first object you are accessing (registration-login), best Users will function as a father to Doctors and Patients tables.
For example - Users - UserName, FirstName, LastName, EMail, PhoneNumber, Company/Address/Active (Many keep encrypted Password in the DB as well).
You can add Role - 1 for Doctor, 2 for Patient (3 for Nurse, 4 for Clinic Manager and so on).
Doctor and Patient can have the same Ids as User or different so you need to add DoctorId/PatientId in Users or add UserId in Doctors/Patients. Again, as Users come first, it's best to go for an option where the Doctor Id can be found from the User.
For easier setup, you can create different Ids for Doctors and Patients, as Doctors have little and Patients many, you can just keep 1000 values to Doctors and 1001+ for Patients, or you can use different sequences / smart increment value, so Doctors can be 1,3,5 and Patients 2,4,6.
Now Doctors can have additional tables such as Skill Sets where Patient have Patient Records.
Note you can download many medical key-value static tables online, as updates are keep coming in the industry, best is to integrate your system with reliable services.
From experience, I believe managing the patient record and the doctor mail box will be your hardest task, as Doctors need to query the full patient history and every day, they also need the latest updates on their patients.
