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