I'm modeling a database, where I have a table called "appointments" (citas in spanish, but I would translate the tables's names for better understanding) where the user can create an appointment to visit a medic (taking values from the medic's table) or to visit an establishment (taking values from "establecimeintos" table), but also I need allow the user to add data directly.

Which can be the best approximation to achive this?, At this moment I'm thinking in add the Id from a medic or establishment as foreign key but i'm no sure.

This is my database diagram: enter image description here

  • Can you give us more information about the context of your application? Now it seems you are trying to store completely different things that just happen to have a very similar name in one table. – Bart van Ingen Schenau Oct 18 '18 at 6:17

If a 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 Appointment and Establishment AND and optional 1 to many relationship between Appointment and Doctor.

The optional relation ship is represented on the diagram by replacing the crow-foot with a | with O on the side of Establishment and Doctor.

For the 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.
| improve this answer | |
  • 1
    The "user" might be the user account the doctor logs in as. If this is true, then a doctor should only have 1 user. That's usually what this sort of foreign key relationship means. – Greg Burghardt Oct 18 '18 at 11:27
  • @GregBurghardt yes, that could be an explanation, but it would be inconsistent with the question's narrative: "where the user create an appointment to visit a medic" – Christophe Oct 18 '18 at 11:35
  • @GregBurghardt thats correct, the BD would be for an Android App, so the current user can register Doctors, but can't see or modify Doctors registered by other user, so doesn't matter if two users register the same Doctor. The Appointment table will work to create alarms or notifications. – Eduardo Corona Oct 18 '18 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.