Regarding database structure

Question

I am somehow new to databases so I still don't know how to organize information properly. Thus, I wanted to ask if this structure is appropriate to the objective I'm trying to achieve:

I am trying to create a database for the management of medical consultations in a number of clinics. Each consultation has associated information related to the ID of the doctor and the ID of the patient, as well as its specialty and the clinic it will take place on. Each clinic has associated a number of medical specialties.

I created the following diagram to illustrate the structure I am thinking to implement (note that only the essential parameters are presented):

My main question is the following:

since each clinic has associated a number of different specialties is it correct to associate to each specialty a foreign key that contains the ID of the clinic that it is associated to?

Thanks!

Answer 1

Everything looks fine with this, but wouldn't you also want the Doctor to have an associated medical specialty as they each can specialize in something different, as is the case in most practices. In that case, you would want your Medical Specialty table to be a dictionary table:

ID    PK
Specialty

And the ID would be used as a one to many with the Doctors and Clinics so each Doctor and Clinic would have the following column:

ID_Specialty    FK

turning the Medical Specialties table into a dictionary table for later reference and expansion. And even if you don't want to attribute specialties to each Doctor, you should still change the Medical Specialties table into a dictionary table to create a one to many relationship with your clinics.