3

SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about:

  • BookDate
  • BookStart

  • BookEnd

    I'm trying to prevent the timeslots from overlapping. To do so, I've managed to bind the 3 attributes together into a composite key, which prevents identical entries from being entered. However, if 2 records like so are entered:

    24/05/18 10:00am 10:15am

24/05/18 10:00am 10:30am

The records are accepted into the system. How do I prevent this? I'm trying to use data validation field right now (Help with preventing double bookings with variable lengths), but I'm not quite sure how to link 2 attributes together within it.

Thanks in advance!

Improve this question
1
  • It seems You use MS Access (question is tagged by [ms-access]). 3 Datetime fields needed 3*8=24 bytes to store. You convert it to string data type with minimal length equal to 16 bytes needed at least 10+16=26 bytes to store. Plus the problem under consideration. Where is Your profit?
    – Akina
    Commented May 17, 2018 at 9:22

1 Answer 1

Reset to default
1

You can only achieve such validation at the database level (when not using forms and VBA) using a CHECK constraint

ALTER TABLE Bookings 
ADD CONSTRAINT NoDoubleBookings CHECK(
       NOT EXISTS(
             SELECT 1 
             FROM Bookings b1, Bookings b2 
             WHERE b1.BookDate + b1.BookStart < b2.BookDate + b2.BookStart 
             AND b1.BookDate + b1.BookEnd > b2.BookDate + b2.BookStart
       )
)

More details on adding check constraints to Access databases can be found here

Improve this answer
5
  • Have you tested / tried this in Access? Does it allow this type of constraints (with subqueries)?
    – ypercubeᵀᴹ
    Commented May 17, 2018 at 9:35
  • @ypercube Access certainly allows these. I haven't tested this specific constraint, but I've used CHECK constraints in Access. You can follow the link to find more information about adding CHECK constraints, since you can't execute them through the normal GUI unless you've set Access to ANSI 92 compatible mode, and you can't execute them using DAO, but you can execute them using ADO.
    – Erik A
    Commented May 17, 2018 at 9:37
  • I'm not asking about CHECK constraint, I'm asking about CHECK constraints with subqueries.
    – ypercubeᵀᴹ
    Commented May 17, 2018 at 10:13
  • @ypercubeᵀᴹ Yes, those are allowed (I wouldn't know why you should use one without a subquery, you can use a normal constraint for that, so I assumed I didn't have to specify that). To ease your mind, I validated this specific rule too now.
    – Erik A
    Commented May 17, 2018 at 10:27
  • What are rules with bookings? You haven't said what is or isn't valid. Are they fixed time slots. (e.g 15 or 30 min blocks?) what days and or hours are they availabe? Mon-Fri? Is it a single serial calendar? I know it's not quite what you've asked for but in my mind I think you need a lookup of available time slots and mark them as avalable or not.
    – Sir Swears-a-lot
    Commented Nov 25, 2019 at 0:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.