help with booking system tables structure

[bazz]

Hi,

building a bookings system and am confused about the tables for showing which rooms are booked. I checked out guelphdad's tutorial but this question seems not to be covered.

tbl_bookings
| booking_id | room_id | start date | end date

tbl_rooms_details
| room_details_id | room_number|


I need your help on which is the more efficient way of doing this.
Should I have all rooms, whether booked or not, in tbl_bookings and search for a room which has a date of 0000-00-00, the default (coz it's available).
OR
should I only have actual bookings in there and so, to find availability, do I need to query the tbl_rooms_details for each room number and then, check for availability by matching room numbers and the dates required in tbl_bookings?

Or is there a better way.

bazz

[Fumigator]

Is this question still pending?

If so, I'd probably go with a "rooms" table, that stores a row for each room, along with maybe some attributes for the room (probably would put the attributes in a key/value style table), then I'd use a "bookings" table to store each booking for a room. Either one row for every night booked, or one row with a begin date and end date for the booking.

You'd know a room is available because there would be no row in the bookings table for a given date.

[bazz]

Thanks Fumigator,

I have fumbled onward so that I now have

tbl_rooms
| room_id | room_number | description | rate | etc

tbl_facilities
| facility_id | facility_name |

tbl_room_facilities
| room_id | facility_id |

and

tbl_bookings_made
| booking_id | room_id | check_in_date | check_out_date |


Can you please explain what you mean by a 'key/value style table'? (I guess you mean a table that enables more than one facility to be related to each room).

bazz

[Fumigator]

key/value is a way of listing an unlimited number of attributes for a given object. For example, PHP's ini file is a key/value style of storage.

Another real-life example is Paypal's IPN transaction. They send a list of values along with the keys to each value to define an entire transaction.


payment_date, '2008-01-01'
business, 'Joe's Tire Shack'
item_name, '15 inch TIre'
item_number, '144934'
quantity, '3'
receiver_email, 'hi@com.com'


The table to store all of these attributes would need just the following columns:

order_id
transaction_key
transaction_value

The only requirement is the key must be unique.

This method allows someone like Paypal to add and subtract attributes as needed, without having to change the database structure. Compare to a table that was defined with the following columns:

order_id
payment_date
business
item_name
item_number
quantity
receiver_email

Now every time a new attribute needs to be stored, you have to change the structure of your table to handle it.

In your case, you could either create a table like this, storing one row per room:

room_id
tv_included
wet_bar
kitchenette
toilet
number_of_beds
bed_size
balcony

Or you could create a table like this, storing several rows per room:

room_id
room_attr_key
room_attr_value

[bazz]

Ahhh good. So that means I could move away from the use of many-to-many tables like this one

rel_id | room_id | facility_id |

where I would then have to search for a room_id to get all its facilities?

I could instead use :

| room_id | facility_key | facility_value |

??


And if that is correct, is it better too when searching a tbale because there are only three cols to search through?

bazz

[Fumigator]

Yeah lookin' good... MySQL will be blazing fast as long as your tables are properly indexed, of course.

[bazz]

Thanks Fumigator,

That has increased my confidence such that I know I have got it pretty good.

:thumbsup:

bazz

[bazz]

So, just to check:

How does this look?

tbl_products
| product_id | product_name | rate | date_from | date_to |

tbl_rooms
| room_id | room_name_or_number |

tbl_when_available

| when_id | product_id | day_of_week |
| 00000001 | 000000001 | Monday |
| 00000002 | 000000001 | Tuesday |


tbl_where_available

| where_id | product_id | room_id |



bazz

[Fumigator]

Do your "when" and "where" available tables indicate where the products are NOT going to be available? If so, then you should name them to indicate as such (i.e. when_reserved, where_reserved).

Also it may make more sense to combine "when" and "where", unless you have situations with a product that it can be reserved for a specific time but you don't know the specific place, or visa-versa. Though I'd still probably combine.

Also you need to work with actual dates, not days of the week. Which Monday? This Monday, or three Mondays away? You can't do date calculations on "Monday", but you can do date calculations on "2008-05-21".

(Keep in mind my opinions are based solely on the info you've provided, so there may be good reasons for your design and if so, then you should stick to your design based on those good reasons)

[bazz]

Thanks Fumigator, I really appreciate your help.

That post combined with your other one, in the other thread, have helped enormously. :thumbsup:

bazz