Database design for dentists

Question

I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists.

Here is a picture of the database design:

Login Table:

Contain login information for dentists (it may be used by multiple dentists online)

The id_logged column is a foreign key of the id of logged in dentists, and it should be in all tables. (To let dentists see only their own patient info and details).

The patient_id column is a foreign key too. And it is taken from patient table and used as foreign key in med_history that represents the general medical information of the patients, patient_info that represents the general information, and the reason of visit and what tasks to do, and in the appointment table.

I have 2 questions:

1- Is there more than that information to add for a dentist database?

2- Is this database correct? Or are there errors?

Answer 1

A few things spring to mind.

1. The patient_info table should be called patient. The (my) patient table should contain patient_name.

2. Every table should have a Primary Key called table_name_id and not just id as you have (makes debugging difficult - which "id" is causing the problem + problems of ambiguity of column names).

3. The medical_history table should hang off patient.

4. The table you call patients should be called appointment.

5. You vary your table names between singular and plural. Pick one and stick to it. I recommend singular.

6. These are just my first impressions.

7. You could also Google things like "Open source medical/dental practice software" and see how they do things in their schemas. Then you might be able to adapt them to your needs. That's the beauty of Open Source software.

p.s. welcome to the forum and good luck with your project! :-)

[Edit in response to OP's request for clarification]

Delete you patients table and replace it with the appointment table.

Your appointment table will have appointment_id, patient_id, dentist_id, date and time.

As regards your dentist friend, that's great that you can run ideas by them, but check out the Open Source s/ware nonetheless.

Another point, I don't know if MySQL is a requirement or not, but if not, I would change to PostgreSQL. Far better for CHECK CONSRINTS - i.e. no overlapping appointments &c.