mardi 4 août 2015

How do I enforce that timeslots for a reservation are consecutive (booking system)?

So I have a design for a simple booking system as follows:

CREATE TABLE interval {
  interval_number INT UNSIGNED NOT NULL,
  interval_start_time TIME,
  interval_end_time TIME,
  PRIMARY KEY (interval_number)
}

CREATE TABLE session {
  session_id INT UNSIGNED NOT NULL,
  interval_number INT UNSIGNED NOT NULL,
  session_date DATE,
  reservation_id INT UNSIGNED NOT NULL
  PRIMARY KEY (session_id)
  CONSTRAINT fk_session_reservation_reservation_id
    FOREIGN KEY
    REFERENCES reservation(reservation_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_session_interval_interval_number
    FOREIGN KEY
    REFERENCES interval(interval_number)
    ON DELETE CASCADE
}

CREATE TABLE reservation {
  reservation_id INT UNSIGNED NOT NULL,
  username VARCHAR (20) NOT NULL,
  reservation_start_time TIME,
  reservation_end_time TIME,
  reservation_date DATE 
}

To summarize, there are intervals of time that can be booked in each day, and a session refers to an interval on a specific date that has been booked. A reservation consists of many sessions, but they must all be consecutive (for example, the user could book 3 sessions tomorrow 11:00 to 12:00, 12:00 to 13:00, and 13:00 to 14:00, and this would be a reservation with reservation_start_time 11:00 and reservation_end_time 14:00). This is so that groups of sessions like this can be summarized more clearly.

My question is, how do I enforce this? As it stands, a reservation could consist of many sessions spread out sporadically on multiple days and multiple times. Is there a better design that could avoid this problem?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire