Databases
StackOverflow2013
Postsdbo
POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
    primarykey
    data
    text
    <p>I'm developing a lawyer booking system, where a person can book an appointment at a given time in a given day (the next lawyer's available day).</p> <p>Let's say it is a <em>ZocDoc for lawyers</em>. The same structure, with appointments based on time: <a href="http://goo.gl/djUZb">http://goo.gl/djUZb</a></p> <p><em>I'm using MySQL and PHP.</em></p> <hr> <h1>The table schema:</h1> <pre><code>CREATE TABLE `laywer_appointments` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `lawyer_id` INT unsigned, `day_of_week` tinyint(3) unsigned DEFAULT '1', `slot_date` date DEFAULT NULL, `slot_time` time DEFAULT NULL, `status` tinyint(4) NOT NULL DEFAULT '0', `client_id` int(11) DEFAULT NULL, -- client_id = NULL means free slot ); </code></pre> <hr> <h1>Point 1)</h1> <p>Each lawyer has default time slots based on the day of week (status = 0 means available). When inserting default slots, I don't provide a date, just day_of_week. Example data:</p> <pre><code>+-----------+-------------+-----------+-----------+ | lawyer_id | day_of_week | slot_time | status | +-----------+-------------+-----------+-----------+ | 1 | 1 | 08:00 | 0 | | 1 | 1 | 08:30 | 0 | | 1 | 1 | 09:00 | 0 | | 1 | 1 | 10:30 | 0 | | 1 | 4 | 14:30 | 0 | | 1 | 4 | 16:40 | 0 | | 2 | 1 | 10:20 | 0 | | 2 | 1 | 14:00 | 0 | | 2 | 3 | 15:50 | 0 | +-----------+-------------+-----------+-----------+ </code></pre> <h1>Point 2)</h1> <p>A lawyer can <strong>add a time slot to a specific day</strong> (<em>even if this day is from a different day of week from his default slots</em>) <strong>and can also lock</strong> (<em>status = -1</em>) one of the default slots in a specific day (i.e. he is on a meeting or he is sick):</p> <pre><code>+-----------+-------------+-----------+-----------+-----------+ | lawyer_id | day_of_week | slot_time | slot_date | status | +-----------+-------------+-----------+-----------+-----------+ | 1 | 1 | 16:00 | 12/03/13 | 0 | | 1 | 6 | 11:00 | 26/04/13 | 0 | | 1 | 6 | 12:00 | 26/04/13 | 0 | | 2 | 1 | 10:00 | 01/01/13 | -1 | +-----------+-------------+-----------+-----------+-----------+ </code></pre> <h1>Point 3)</h1> <p>Then we have appointments booked. In this case we fill the slot_date and the client_id:</p> <pre><code>+-----------+-------------+-----------+-----------+-----------+ | lawyer_id | day_of_week | slot_time | slot_date | client_id | +-----------+-------------+-----------+-----------+-----------+ | 1 | 1 | 10:30 | 12/03/13 | 10 | +-----------+-------------+-----------+-----------+-----------+ </code></pre> <p>As an example, with the above booking and assuming it is still 6:30 of the same day (12/03/13), the free available slots that have to be printed are:</p> <pre><code>8:00 - default slot 8:30 - default slot 9:00 - default slot 16:00 - Specific slot inserted in point 2 for 12/03/13 </code></pre> <hr> <h1>The problem:</h1> <p>I have to return the next available date and the related free times (default ones, specific ones minus locked ones and booked ones). I can't just say "return times from Monday, 10/10/13".</p> <p>In a search results page, <strong>I'll list all lawyers and the availability time table for each</strong>. So that means each lawyer will have a different time table every time a search is made.</p> <p>I can't simply say <em>"SELECT time FROM [bunch of joins] WHERE date = today"</em>.</p> <p>I came with this query which ignores slots that are locked (status = -1) or booked (client_id not null), but of course it won't return the free times for the closest day with available times (or from today):</p> <pre><code>SELECT p.day_of_week, p.slot_date, p.slot_time FROM laywer_appointments p WHERE p.client_id IS NULL AND p.status = 0 AND p.slot_time NOT IN ( SELECT s.slot_time FROM laywer_appointments s WHERE (s.slot_date IS NOT NULL AND s.client_id IS NOT NULL OR s.status = -1) AND s.day_of_week = p.day_of_week ) GROUP BY p.day_of_week, p.slot_date, p.slot_time ORDER BY p.day_of_week ASC, p.slot_time ASC; </code></pre> <p><strong>Another problem:</strong> if today is day_of_week = 5, but the next available day_of_week for a given lawyer is 2, how can I query that? </p> <p>How to return the next closest and available day_of_week and aggregate to just return times from this day, not all days?</p> <h1>One possible solution</h1> <p>One thing I came with was to create 3 tables instead of one:</p> <ul> <li>default_slots: 3 columns: lawyer_id, day_of_week, time</li> <li>slots: laywer_id, day_of_week, time, date, status</li> <li>appointments: all info regarding a booked appointment</li> </ul> <p><strong>Then I'll store ALL free time slots for every day of the actual date up to an year in the slots table for every lawyer.</strong> (taken time slots from default_slots).</p> <pre><code>+-----------+-------------+-----------+-----------+-----------+ | lawyer_id | day_of_week | slot_time | slot_date | status | +-----------+-------------+-----------+-----------+-----------+ | 1 | 1 | 16:00 | 12/03/13 | 0 | | 1 | 1 | 16:00 | 12/03/13 | 0 | | 1 | 2 | 08:00 | 13/03/13 | 0 | | 1 | 2 | 09:00 | 13/03/13 | 0 | ... next week | 1 | 1 | 16:00 | 19/03/13 | 0 | | 1 | 1 | 16:00 | 19/03/13 | 0 | | 1 | 2 | 08:00 | 20/03/13 | 0 | | 1 | 2 | 09:00 | 20/03/13 | 0 | ... up to an year | 1 | 1 | 16:00 | 20/03/14 | 0 | | 1 | 1 | 16:00 | 20/03/14 | 0 | | 1 | 2 | 08:00 | 21/03/14 | 0 | | 1 | 2 | 09:00 | 21/03/14 | 0 | +-----------+-------------+-----------+-----------+-----------+ </code></pre> <p>I'll also have some cron jobs that run every week that adds another week of free slot records in the table slots and also remove past records to reduce table size and unused data.</p> <p>A lawyer will also be able to lock a time directly into slots, as well add specific times (point 2).</p> <p><strong>For the listing, it will be a matter of getting the slots for a date equal or greater than today with free times, since every time of every date will have a row</strong>.</p> <p>Implications on this solution: 1) Day one we will have 2500 lawyers (2nd month around 6000). Assuming 8 possible slots/daily X 20 days of work/month X 12 months = <strong>1920 slot records per lawyer.</strong></p> <p><strong>2500 laywers x 1920 records = 4,8 million records on day one.</strong> (~12M the second month)</p> <p>Those records will be UPDATED, INSERTED and DELETED all the time. <em>The slots table has some indexes, so I can't imagine write operations being made constantly on a table with 12M+ records and some indexes.</em> Indexes being updated every second doesn't see smart to me.</p> <p><strong>I really can't come with a reasonable and scalable solution. My solution with one table only could work, but I can't think in a way of querying that at all. And the denormalized slots table will be huge, while needing constant write operations.</strong></p> <p>Any tips?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlfredBaudisch
    1. USAlfredBaudisch
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
      1. UStoystory
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload