Greetings. Several years ago, if only on a whim, I created a web site to chronicle my travels and share my photos and stories with people from around the world. Now five years, and as many trips later, here I am living in Budapest Hungary. Here is my situation. I have been programming using ASP and Javascript for the past three or four years, and am not bad at it. Also, I have been using MS Access for my data storage. I want to graduate from Access and begin using something like mySql or SQL Server. (any suggestions) Here is a design I have laid out in my head for a database and would greatly appreciate any feedback from any gurus out there, if only telling me I am on the right track. Here goes -

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.

You have an sound idea but I would design your tables differently.

Hotel(HotelID, Attributes...)

Features(FeatureID, FeatureName, Attributes...)

HotelFeatures(HotelID, FeatureID)

Room(RoomID, Attributes...)

Amenities(AmenityID, AmenityName, Attributes...)

RoomAmenities(RoomID, AmenityID)

What I am saying is have a master list of amenities and a master list of features. Do not create a table with a column for each Amenity or Feature as it violates first normal form.

Each hotel would have its own seasons.

Season(SeasonID, HotelID, SeasonName, Attributes...)

RoomSeason(RoomID, SeasonID, Rate)

That's about as much as I can whip together on a long weekend. Best of luck.

Thanks so much, it is a big help to me and I will study the code you gave me and try to implement it in my design.
Tom