enter image description here

I create for the first time a database, please tell me how to implement such a thing, so that, in essence, it is possible to write what time to make an appointment for a doctor, but in the restriction on the doctor’s schedule. thank you in advance

  • I would review the data schema and change the relationship between Schedule Entities and Physician, remove connection between Physician and Record on Reception, and add relationship between Record and Schedule - Anatol
  • Well, in general, it can be solved with the help of CHECK CONSTRAINT - Anatol

2 answers 2

I see a lot of little things in the scheme that seem illogical to me. Think about your structure and decide, maybe I'm right:

  • ID_part and â„–_part cannot be a sign of a doctor, because one doctor can work at several sites at once (to do part-time work), can change areas. The same with the department. A sign of a doctor is only a specialty, and that is not a fact; perhaps some doctors may combine work in several specialties.
  • As a result, the essence of the site can be singled out separately, and it’s possible that the schedule should be tied to the site, but also include a link to the doctor so that it is clear on which particular day which doctor is on duty at this site.
  • Making an appointment is logical to link to a schedule - a schedule entry is the period when the section is “open”, the record must fall during this period. Links to the doctor while not needed - because it is already in the schedule. Need reference to the patient and the time of admission.

    Alternatively, you can hang the trigger, which, when inserted, will check the compliance schedule.

    PS Do not use Russian letters in the names of attributes and entities.

    It is extremely inconvenient to constantly switch from one layout to another when writing queries ...