User (Usuario) can have an
Appointment (Citas) with a specific
Doctor (Medico) or at a specific
Establishment (Establecimiento), then you need to have an optional 1 to many relationship between
Establishment AND and optional 1 to many relationship between
The optional relation ship is represented on the diagram by replacing the crow-foot with a
O on the side of
Appointment table, this means to have a field which is a foreign key for both of them. And these fields shall be allowed to be null. THis will give you full flexibility: you can add the appointment without anything, you can set the doctor or the establishment or both.
By the occasion, I'd like to draw your attention at two more points (unrelated to your question):
- Your diagram says that a
Doctor has only one
User (this is why a
usario_id is generated in the
Doctor table). I think this is wrong (unless your system manages a clinic for millionaires who can hire their own full time private doctor): you need to change this relation to a many to many relationship.
- I wonder if this direct relationship is necessary, since the
Appointment will de facto be an indirect many to many relationship between doctors and patients.