I have a link on my web site for Hotels. I want to be able to provide a means to display and query hotels from a database. I envision (at start) having four tables.
TABLE ONE - HOTELS: will be for the hotel itself. Typical fields will be name, address, city, country, etc.
TABLE TWO - ROOMS: This would be a table of rooms. I have pondered this over and over and I see this as practical. Each hotel will have different types and amounts of rooms, each room having a different rate. Hotel owners, after entering hotel information in Table One, will then add their rooms. This table will have a foreign key, which will hold the value of the primary key from HOTELS. So, each hotel can add as many hotels as they desire.
TALBE THREE - HOTEL FEATURES: Okay,this table will hold such features as Golf, Tennis, Massage, Room Service, Satellite TV, Handicap Accessible, etc. I understand that this is the best way to set up a database for querying the same.
TABLE FOUR - ROOM FEATURES: This table will hold a pre-defined set of boolean fields for each room. As a room will be added, the owner will then choose what features the room offers. Ex. TV, Stereo, Jacuzzi, Refrigerator, View, Vibrating bed, Walk-In Closet, Kitchen, etc., etc.
That is my design so far. I anticipate down the road adding such Tables as Reservations, Billings, etc. One thing that challenges me is this. Most hotels will have a High and Low Season. This would be where each room may have a rate for that particular season. How could I incorporate this into my design? What if they have more than two season, such as a "shoulder" season, or even an unlimited amount of seasons? What if they charge special rates for holidays? Also, I envision having hotels upload images for their hotel, but for the general hotel areas and specific rooms.
I would be forever grateful if someone could reply to this and let me know if I am on the right track. Thanks so much.