MySQL DB structure for booking holidays??

[pavsid]

Hi,

Does anybody know what is the best structure the DB would have in order to store bookings and therefore reveal available slots for a holiday villa site?

Thanks.

[Phill Pafford]

Its all what information you need to store, but I know MySQL has a workbench tool that can help in creating the table structure. They also give free web seminars on how to use the tool.

[aj_nsc]

Below is an extremely minimalistic table that works fine in an application that I recently created for showing booked and available dates for a vacation cottage.


The date field in the code below was actually just the unixtime of a given day.


--
-- Table structure for table `bookings`
--

CREATE TABLE IF NOT EXISTS `bookings` (
`date` int(11) NOT NULL,
KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


The user interface for this was a php-generated calendar. As each cell (i.e. day) in the calendar was being generated, it would query the database to determine if the cottage was booked on that day. If the cottage was booked, I would change the background color of that cell (i.e. day) in the calendar to show the visitors that the cottage was booked that day. If there was no booking, then I would leave the background color normal and they would know that the cottage was not booked that day.


It really all depends on how much information you want to store (e.g. do you want to show the villa booked for partial days) and how to present it to the visitor. The above approach worked fine for me and you can't get much simpler than this.

[svidgen]

I had to create a small booking script for birthday parties at roller rinks. It allows the creation of "sessions" with associated "weights" that can be booked, as well as an exceptions table to filter sessions that fall on particular days and so on. So, a session from 3:00p to 6:00p on Tuesdays in May might have a weight of 3, meaning 3 parties (or 1 party of weight 2 and 1 of weight 1, or w/e) can be booked for that session.

This table is used to record all of the appointments made and how much weight they carry (how many blocks/sessions they fill):
CREATE TABLE `appointments` (
`appointment_id` int(11) unsigned NOT NULL auto_increment,
`start` datetime default NULL,
`end` datetime default NULL,
`weight` tinyint(3) unsigned default NULL,
`party_id` int(11) unsigned default NULL,
`midnightflag` int(1) default '0',
`status` enum('pending_confirm','pending_deposit','confirmed','deleted') default NULL,
`timestamp` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`appointment_id`),
KEY `start` (`start`),
KEY `end` (`end`)
) ENGINE=InnoDB
This table is used to store when sessions are available for booking. It keeps the start and end time of each session as well as the weight each one can hold:
CREATE TABLE `blocks` (
`block_id` int(5) unsigned NOT NULL auto_increment,
`day` enum('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday','%') default NULL,
`start` time default NULL,
`end` time default NULL,
`weight` tinyint(3) unsigned default NULL,
`midnightflag` int(1) default '0',
`start_day` char(2) default NULL,
`end_day` char(2) default NULL,
`start_month` enum('January','February','March','April','May','June','July','August','September','October','Novemb er','December','%') default NULL,
`end_month` enum('January','February','March','April','May','June','July','August','September','October','Novemb er','December','%') default NULL,
`timestamp` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`block_id`),
KEY `day` (`day`)
) ENGINE=InnoDB
And this table is for listing holidays and other special days that the rink is closed. This is useful if Tuesdays in May normally have a session, but the 2nd Tuesday happens to be a renovation day or something:
CREATE TABLE `exceptions` (
`exception_id` int(11) unsigned NOT NULL auto_increment,
`start_date` date default NULL,
`end_date` date default NULL,
`timestamp` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`exception_id`),
KEY `edate` (`start_date`),
KEY `end_date` (`end_date`),
KEY `start_date` (`start_date`)
) ENGINE=InnoDB
So, I think you could implement similar tables. A lot of the values in my tables won't apply to you (some are even obsolete for my project). And you'll obviously be dealing with start and end dates rather than times.

Also, when I created this gadget, I wasn't concerned with timezone issues. You might find it useful to use all timestamp fields with no default or 'on update' clause. This will force all times to be converted to GMT/UTC. So, when you connect, you can set the timezone you're connecting from and all the dates will appear in the local timezone no matter where you connect from. Or, you can just set a static timezone and still rest assured that all the dates/times are correct relative to each other.

Does that help at all?

[pavsid]

Yes thats a great help, thanks, well, i say it's a great help but really it's just told me that i need to go and do some learning on the date and datetime data types of mysql as i didn't know these existed! Once i've understood how to use these then hopefully i'll understand how to implement them and your examples will mean a lot more to me.

Thanks again for now, but i'll probably be back!....