Quick dscription of a problem
first of all I am still learning so please be understanding. Recently I've come across a post where someone was proposing nice schema for storing recurring events. I found it interesting and decided to expand that for learning purposes. But I endded up with a "circular" schema that really freaks me out. Let's look at that through the prism of appointment system (let's say to a doctor).
Company - a private clinic that employs doctors and let patients make appointemnts.
Frame - a range in time when an event occurs. F.e. an appointemnt in a company calendar might be an event, that is represented by a row in the
frame table. It's possible to create a repetitive
frame that happens f.e. every monday.
Doctor - obviously a person that can be assigned to a given frame (appointment) and company.
A company can have a calendar. A calendar can have "appointments"/"events". Each event occurs within a given time frame. Each such an "event" is held as a "range in time" in a
frametable (along with meta info). Thus one company can have many "appointments"/"events" or many "frame"s.
A frame can be in many companies (many calendars). Because a frame, is basically just a
end_date. F.e. frame
2018-01-01 09:00:00can be defined in calendar of a "companyA" and a "companyB".
Doctor can be a member (an employee) of many comapnies (clinics). that is obvious. A company (clinic) can have many doctors, that's obvious too.
But an employee of a clinic, can assign a
frame(make an appointemnt on behalf of a patient). Let's assume that, one
framecan have assigned many doctors (f.e. difficult DDX, like in a tv show "Dr. House"). Thus one evetn/appointement/frame can have many doctors.
A dcotor can be in many
frames, be assigned to many appointments. That's obvious.
Is my schema acceptable ?
Is "circular square of tables" acceptable in that case ?
When company A and company B both are assigned to a frame (2018-03-26 09:00 - 2018-03-26 12:00), that means that company A has an event in the frame, and company B has a completely distinct event in that same frame.
Company A An appointment between Ms.Alice and Dr.Frank at 5 o'clock (frame: 2018-01-01 05:00:00 PM)
Company B An appointment between Mr.Bob and Dr.Smith at 5 o'clock (frame: 2018-01-01 05:00:00 PM)
Two different companies, two different calendars, two different events, one (time)
Corrected design (according to review in an answer):