Page 1 of 1

Calendar - Database Driven

Posted: Tue Apr 18, 2006 5:14 pm
by denz
HI All

I'm just in the process of designing a new application, but having troubles getting my head around the schema design.

I'm creating a date based booking system where customers can book a room for as many or few full days as they wish. There is of course a limit to the number of rooms available so i need to work out when it's full and decline a booking if not all days are available.

To work out if a booking would be possible (for example if they were booking 5 days and all 5 days have rooms free) would i use some kind of function to query the db for each day to determine what days were free, or would it be better to have an addtional table in the DB which would be updated whenever a booking is made with one row per day and the number of bookings on that row?

Or is there a better way!??!! :? :?


any help would be much appreciated!

Thansk

Posted: Tue Apr 18, 2006 5:26 pm
by feyd
I think I would have a single bookings table. This table would reference the room it applies to and a guest record to whom is renting/reserved the room. It would contain scheduled start date and end date fields. To find if a room is available, I'd perform a query joined against the rooms table to find rooms that do not have entries where the start date or end date fall between the dates requested. Seems pretty straight forward.

edit: would also need logic to check if the room's start and end dates surround the dates requested as well.

Posted: Tue Apr 18, 2006 6:08 pm
by Christopher
I implemented something like this once. As crazy as it sounds, I ended up adding a record for each room/day. I had two tables, on for bookings and one for rooms/days. Every room/day had a bookingID. The reason I did that was because I needed to associate different people and resources with rooms each day. I deleted old records in the rooms/days table so it was never very large.

There are some open source room booking systems around. Check Freshmeat or Sourceforge.

Posted: Wed Apr 19, 2006 12:54 am
by denz
The number is rooms is likely to increase in time, also when people book they wont be able to select the room number as all rooms are effectivly the same.

Does this make things harder?

Posted: Wed Apr 19, 2006 1:12 am
by feyd
At this point the logic is just looking for rooms that don't have bookings that overlap the requested dates. The room number doesn't matter to it at all, nor does the number of rooms possible.

Posted: Wed Apr 19, 2006 3:30 am
by Ollie Saunders
As crazy as it sounds, I ended up adding a record for each room/day.
Yeah thinking about about it can't you just use WHERE BETWEEN chk_in AND chk_out AND room_id != 435345

have some sql:

Code: Select all

CREATE TABLE booking (
	boid		INT UNSIGNED NOT NULL AUTO_INCREMENT,
				 PRIMARY KEY(boid),
	name		VARCHAR(255) NOT NULL, -- put other details here if you wish or better...
	contact	VARCHAR(255) NOT NULL, -- ...yet store them in a separate users table
	chk_in	 DATE NOT NULL,
	chk_out   DATE NOT NULL,
	room_id	INT UNSIGNED,
				 FOREIGN KEY (room_id) REFERENCES room(roid)
) ENGINE = InnoDB;

CREATE TABLE room (
	roid	INT UNSIGNED NOT NULL AUTO_INCREMENT,
	other_data_about_room	VARCHAR(255) ...
) ENGINE = InnoDB;
probably not complete, i got bored.

Posted: Wed Apr 19, 2006 3:26 pm
by denz
thanks guys. i think that give me lots to be getting on with!

Posted: Wed Apr 19, 2006 4:07 pm
by denz
i'll admit i'm a little stuck

ok, lets assume i only have one room. someone has booked the room from the 1st may - 5th may. someone else wants to book from the 2nd may 4th, how on earth could i query this to see it's already booked?

Posted: Wed Apr 19, 2006 5:36 pm
by denz
arborint wrote:As crazy as it sounds, I ended up adding a record for each room/day.
i have been thinking this could be the best way to go, but considering i may have 68 rooms (or more), each booked out every day, 7 days a week the number if records in the table would be massive. I would need to keep some kind of record of the bookings for some time so the database would get out of control.

I can't however think of another way which i would be able to query every day within a booking time frame to see if we are full.

:?

Posted: Wed Apr 19, 2006 5:44 pm
by Ollie Saunders
:oops: 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.

Posted: Thu Apr 20, 2006 9:37 am
by denz
Thanks ole

i think i get it now! :D