The first thing that catches my eye is what appear to be separate tables for each exam. This could get rather difficult to maintain in the future: if the structure of exams changes you need to update n tables. Also, any aggregate queries that cover all exams for 1 patient will need to join n tables.
I'd suggest a structure like this:
patient_exams
-------------
patient_exam_id (PK)
visit_id (FK)
patient_id (FK)
study_id (Fk)
exam_seq_num
(other fields)
Use exam_seq_num to track which exam number the record is for a patient. You can use a composite key containing patient_id, visit_id, study_id, exam_seq_num to ensure that you don't get exams for a patient with duplicate sequence numbers. You'll still need a bit of code to create the correct sequence number, maybe an on-insert trigger.
UPDATE:
Ok, so now it's clear that the exame tables are actually for different types of exams.
You could have something like
base_exam
---------
id (PK)
patient_id (FK)
exam_date
(other stuff)
Lumbar_exam_details
-------------------
lumbar_exam_id (PK)
base_exam_id (FK to base_exam.id)
(other specific fields)
blood_exam_details
------------------
blood_exam_id (pk)
base_exam_id (FK to base_exam.id)
(other specific fields)
All of your exam detail tables reference the base_exam table, which stores common fields for all exams (such as the date of the exam, the patient who was examined, etc...).
If you really want to have a "display name" for exam types, I would do that in a view that overlays the specific exam table. For example, the query for lumbar_exam_view might look like:
SELECT *, "Lumbar Exam" AS DISPLAY_NAME
FROM LUMBAR_EXAM_DETAILS
Use this view in any queries/reports on lumbar_exam_details you will have access to display_name anywhere that you want the user-friendly string.
If you need the display name to be stored as actual data, you can add an exam_type_id field to base_exam and then have it point to an exam_type table:
exam_type
---------
id
display_name
Data:
exam_types
ID | display_name
------------------
1 | Lumbar Exam
2 | Blood test
Now your base exam records have an ID that points them to the correct user-friendly string. Note that this does not ensure that the exam detail record is of the correct type (i.e. it is possible, for example, to have a base_exam record that is referenced by lumbar_exam_details, but the base_exame record erroneously references the display name "Blood Test") - it only works on the display name.
You don't need composite keys to enforce your referential integrity in your case. The reason is that you have a pretty straight-forward three tier hierarchy:
PATIENT
+
|
^
TEST
+
|
^
SAMPLE
Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.
This works because each sample record needs a test record and each test needs a patient. If you delete a patient, their tests must be cascade deleted. If you delete a test its samples must be cascade deleted. Therefore you don't need patient_id in the TEST table. All you need is to declare cascade deletion on your foreign keys, like so...
...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...
Best Answer
A few things spring to mind.
The patient_info table should be called patient. The (my) patient table should contain patient_name.
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).
The medical_history table should hang off patient.
The table you call patients should be called appointment.
You vary your table names between singular and plural. Pick one and stick to it. I recommend singular.
These are just my first impressions.
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.