Database Design – Is My DB Schema Alright?

database-designschema

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).

Presentation of my approach
My db design

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.

  1. 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 frame table (along with meta info). Thus one
    company can have many "appointments"/"events" or many "frame"s.

  2. A frame can be in many companies (many calendars). Because a frame,
    is basically just a start_date and an end_date. F.e. frame
    2018-01-01 08:00:00 – 2018-01-01 09:00:00 can be defined in
    calendar of a "companyA" and a "companyB".

  3. 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.

  4. But an employee of a clinic, can assign a doctor to a frame (make an appointemnt on behalf of a patient). Let's assume that, one frame can have assigned many doctors (f.e. difficult DDX, like in a tv show "Dr. House"). Thus one evetn/appointement/frame can have many doctors.

  5. A dcotor can be in many frames, be assigned to many appointments. That's obvious.

My questions

  1. Is my schema acceptable ?

  2. Is "circular square of tables" acceptable in that case ?

Update:

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) frame.

Corrected design (according to review in an answer):

enter image description here

Best Answer

I see two potential issues:

Clarity on frames

I understand that a frame represents one (or more) intervals, as established by a company.

I believe you originally intended a frame to be a block of time, and to allow multiple companies to use it. The potential problem with that is - what if one company needs to alter that frame? You basically have to create a second frame, and migrate over only the references for the one company to the old frame. This is possible, but would be a maintenance hassle (and would slow things down while you did it).

I also note that a frame can include multiple time intervals, and repeating time frames (allowing, I assume, you to set up weekly standing "appointments" where appropriate). If a frame represents a specific event (even if that "event" is a recurring weekly appointment, or a five-day conference), this shouldn't be a problem; If multiple events are re-using the same frame, then the maintenance issue mentioned above would grow even worse.

If a frame belongs to just one company, you don't really need the company_frame table; you can have the company_id in the frame table directly.

NOTE: One specific clinic may have many rooms, and many doctors, each of whom may have different appointments at the same time. It may be worthwhile to add another distinguishing factor to a frame - perhaps a location ("Room 2" in a doctor's office; "Conference Suite 100"; whatever's appropriate) to help differentiate. In some cases, it may be sufficient to use the doctor and the time interval (in a doctor's office, "Dr. Cameron, 15:00 - 15:30, 2018-03-23" should be distinctive); in other cases, you might need something more.

Data consistency

The next thing that comes to mind is the potential for data consistency issues. This is where the "circular" design comes into play.

In theory, with this design, nothing guarantees that a doctor associated with a frame is connected to the same company as the one that "owns" the frame. However, logically, all doctors associated with a given frame should belong to the same company as the frame.

Based on this, I think that it makes more sense that frame_doctor actually be frame_co_doctor, and that it be used to maintain a many-to-many relationship between company_doctor and frame instead of between doctor and frame (doctor would only be connected to anything else via company_doctor then).

Presumably, if a given doctor stopped working for company X, he would no longer be tied to the frames belonging to that company; he might still be connected to frames owned by other companies, though. And, company_doctor and frame would both have the company_id, making it easy to verify that the correct records are being linked - if company_doctor.company_id <> frame.company_id, then you shouldn't create the frame_co_doctor row.

Those are the biggest potential issues I see with the design as it stand now, and how I'd resolve them, based on what I know of your requirements. I may, of course, have misunderstood a requirement, or there may be some you haven't noted as yet, that would invalidate these approaches, but they're the way I'd look at it.

Related Question