
err hehe yeah ermm hmm
basically this:
Yeah thinking about about it can't you just use WHERE BETWEEN chk_in AND chk_out AND room_id != 435345
is utter wank.
but this:
As crazy as it sounds, I ended up adding a record for each room/day.
is a really good idea
You know you can store dates as ints if you like using TO_DAYS(). There is FROM_DAYS() as well so you can do day arthmetic in SQL these will speed things up and days are your most atomic unit of measurement in this case.
Code: Select all
mysql> select to_days(now());
+----------------+
| to_days(now()) |
+----------------+
| 732785 |
+----------------+
1 row in set (0.00 sec)
Basically I think the process is
- When a booking is made spending May 1st to May 20th insert 20 records storing the dates May 1st through May 20th
- To select all the bookings you can read all the integers back and outputting the existing bookings will be really simple.
- To check a booking can be made on a certain date you would have to loop through all the days that booking is for and check there isn't already a booking in that room
Here's your table structure you'll need innodb enabled to use this.
Code: Select all
CREATE TABLE room (
roid INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(roid),
data VARCHAR(255) -- whatever other data
) ENGINE = InnoDB;
CREATE TABLE user (
usid INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(usid),
data VARCHAR(255) -- whatever other data
) ENGINE = InnoDB;
CREATE TABLE booking (
boid INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(boid),
room_id INT UNSIGNED NOT NULL,
FOREIGN KEY (room_id) REFERENCES room(roid) ON DELETE CASCADE,
user_id INT UNSIGNED NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(usid) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE days (
daid INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(daid),
day INT UNSIGNED NOT NULL, -- you may prefer to use a DATE column type
booking_id INT UNSIGNED NOT NULL,
FOREIGN KEY (booking_id) REFERENCES booking(boid) ON DELETE CASCADE
) ENGINE = InnoDB;
-- remember foreign keys automatically create indices so if you don't want to
-- use foreign keys make sure you put indices in their place (and you can ommit
-- the engine clause) otherwise it'll be like waiting for the titanic to sink
-- i.e. no fun
don't say i'm not good to ya. Don't worry about having gazillion records in days because it will be indexed on booking_id so you'll be able to get all the days from a booking with relative ease.