Calendar - Database Driven

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
denz
Forum Newbie
Posts: 18
Joined: Fri Jun 24, 2005 4:18 pm

Calendar - Database Driven

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
denz
Forum Newbie
Posts: 18
Joined: Fri Jun 24, 2005 4:18 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
denz
Forum Newbie
Posts: 18
Joined: Fri Jun 24, 2005 4:18 pm

Post by denz »

thanks guys. i think that give me lots to be getting on with!
denz
Forum Newbie
Posts: 18
Joined: Fri Jun 24, 2005 4:18 pm

Post 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?
denz
Forum Newbie
Posts: 18
Joined: Fri Jun 24, 2005 4:18 pm

Post 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.

:?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
denz
Forum Newbie
Posts: 18
Joined: Fri Jun 24, 2005 4:18 pm

Post by denz »

Thanks ole

i think i get it now! :D
Post Reply