2

I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine.
A medical center has 14-16 offices/rooms that are rented (monthly rate) to doctors from various medical specialties or areas: oncologists, physical therapists, general practitioners, psychologists, etc.

All the areas have their session time assignment: oncol. -> 30 minutes, psycho. -> 45 minutes and so on. All the patients do long treatments, so 99.9% of assigned shifts are recurring through time

So, the question would be:
How could I build the optimal design so I can store:

  • room assignment for each proffesional from monday to friday. eg:

    1. John (oncologist): MON 15:00 to 20:00, TU: 09:00 to 12:00, WED: 08:00 to 13:00
    2. Mary (psychologist): MON 09:00 to 12:00 and 16:00 to 19:00, THU: 08:00 to 13:00
    3. Michael (general): FRI: 10:00 to 16:00
    4. Henry (dentist): TU: 09:00 to 12:00 and 14:00 to 19:00, WED: 09:00 to 12:00
  • Each proffesional can change their assigned days (in case there's an office available at the desired day/time) through the months, so I'd be able to keep historical data like:
    Which patients had Dr. Phillips treated in Nov. 2013, on which rooms he worked, and which days/hours had been rented to him on that time, etc.

Thanks a lot in advance

3

You could have a bookings table like this:

bookings
--------
  id (PK)
  user_id (FK to table with all medical professionals)
  room_id (FK to table with all rooms)
  start_date - datetime value
  end_date - datetime value

This structure will let you track which professional is using which room, and when:

bookings
--------
id  | user_id  | room_id  | start_date    | end_date
---------------------------------------------------------
1   | 001      | 123      | 20140101T1030 | 20140101T1045
2   | 001      | 123      | 20140101T1100 | 20140101T1115
3   | 002      | 120      | 20140101T1000 | 20140101T1100

To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".

professionals
-------------
  id
  name
  deleted
  (other fields...)

rooms
-----
  id
  deleted
  (other fields)

Example:

professionals
-------------
id  | name       | deleted
----+------------+--------
1   | Dr. Philips| N
2   | Dr. Nick R.| Y

rooms
-----
id  | deleted
----+--------
123 | N
120 | N
837 | Y

To handle recurring appointments, you could start with something like this:

recurring_bookings
------------------
  id (PK)
  user_id (FK to med. professionals)
  room_id (FK to rooms)
  day_of_week (could be FK to table/enum of days of week)
  start_time
  end_time (or you could have a "duration", in minutes, if you prefer)
  start_of_recurrence
  end_of_recurrence

recurring_bookings
------------------
id   | user_id    | room_id   | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1    | 001        | 120       | MONDAY      | 1030       | 1100     | 2014-01-01          | 2014-12-31
2    | 001        | 120       | WEDNESDAY   | 1030       | 1100     | 2014-01-01          | 2014-12-31

The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31

The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings to populate all appointments for a user in bookings, and then rmeove or modify them from bookings as needed. This requires you to pre-populate a lot of data into your main bookings table (based on start_of_recurrence and end_of_recurrence), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".

I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).

  • Thanks for your help. What do you think about adding an "active" (bool) field in the bookings table, so I know that rental is historical or not? – Enrique Jun 24 '14 at 19:23
  • @Enrique: Could you just compare the start and end time of the current date to see if the booking is in the past, present, or future? Or is that not quite what you meant? – FrustratedWithFormsDesigner Jun 24 '14 at 19:24
  • The booking is made until something happens, so in fact I wouldn't need the end_date field. Something that could trigger change would be: changing day/time assigned, changing office assigned. Some proffessionals get other jobs and need to re-arrange time/offices, etc. – Enrique Jun 24 '14 at 23:24
  • 1
    @Enrique: Maybe instead of end_date you'd want a field to track duration of use? You still need a way to know if two bookings may potentially overlap, so you'd need end_date or duration (from start_date). – FrustratedWithFormsDesigner Jun 25 '14 at 3:36
  • 1
    @Enrique: I've updated with a bit about how to handle recurring bookings. – FrustratedWithFormsDesigner Jun 26 '14 at 15:34

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.