SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding an available appointment slot

    Got a bit of a tricky query (or group of queries if necessary) that I need to write. Thought I'd pop back to SPF for the first time in aaaages and see if the community can weigh in

    Code MySQL:
    CREATE TABLE appointment_slots
    ( id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    , tutor_id  BIGINT       NOT NULL
    , starts_at DATETIME     NOT NULL
    , ends_at   DATETIME     NOT NULL
    );
     
    CREATE TABLE appointment_slot_bookings
    ( id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    , slot_id    INT UNSIGNED NOT NULL
    , start_date DATETIME     NOT NULL
    , end_date   DATETIME     NOT NULL
    , booking_id BIGINT       NOT NULL
    );

    Every few days, each tutor logs in and specifies upcoming dates and times she is available for appointments.

    (A constraint in the frontend code dictates that each appointment slot be 15 minutes long, and between 9am and 7pm. I guess this is largely irrelevant to the problem at hand though.)

    Given a user_id (e.g. 3), and a required appointment length (e.g. 40 minutes), I want to find all available contiguous 40-minute windows that have neither been booked nor overlap any existing bookings, that fall within the next two weeks.

    I don't usually have many issues tackling SQL queries but have to admit this one has got me stumped before I've even been able to write a single line of code!

    Any advice on where to begin would be greatly appreciated.

    Please note table structure is still flexible at this point. If I should be designing my database differently, please say so!

    Also, I realise the terminology and the nature of the problem suggest a homework assignment. Please be assured this is not the case!
    Sam Hastings

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just realised my table logic is flawed slightly; slot_id in appointment_slot_bookings ought to read tutor_id!
    Sam Hastings

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SJH View Post
    Just realised my table logic is flawed slightly; slot_id in appointment_slot_bookings ought to read tutor_id!
    i don't think i can help (yet) because i don't understand the 15-minute slots and why you are looking for 40-minute windows

    perhaps you could upload some (meaningful) sample data for both tables? in sql dump format, so we can experiment on the queries ourselves

    meanwhile, i like how you've anticipated scaling this system up, by being able to handle more than 2 billion tutors -- but if that comes to pass, and if each tutor has more than one slot, you're gonna need to upsize the auto_increments as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •