Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

A customer can make a reservation for an accommodation, the accommodation can be in a resort or elsewhere.

So i would say that a resort can have different types of accommodations, ie. Chalets, apartments bungalows, caravans, lodges, hotels, etc...

and if a customer wants to make a reservation for these types of accommodation but not in a resort.

How would i model it?

  • ps. lets assume that elsewhere* means a hotel outside the resort

ERD Diagram-

share|improve this question
up vote 3 down vote accepted

Is "resort" a special type of entity? If not then you could model this as:

  CustomerID - unique primary key
  Name - text
  (other customer fields)

  AccomodationID - unique primary key
  LocationID - foreign key to "where" the accomodation is
  AccomodationTypeID - foreign key to types of accomodations
  (other stuff such as "# bathrooms", "# beds...")

  LocationID - unique primiary key
  LocationTypeID - foreign key to location type
  (other fields)

  ReservationID - unique primary key
  CustomerID - foreign key to customer
  AccomodationID - foreign key to accomodation
  (any other fields you need)

  Description (such as "Resort", "campground",...)

  Description (such as "Lean-to", "chalet", "motel",...)
share|improve this answer
What do you mean by special – D.G Mar 7 '12 at 19:56
@JoseDavidGarciaLlanos: You say "the accommodation can be in a resort or elsewhere". Does this mean that you want to treat Resorts differently than whatever would be considered "elsewhere", do you want to have exactly two options: "Resort" and "Elsewhere", or was "Resort" just one of many possible examples for where an accomodation could be? – FrustratedWithFormsDesigner Mar 7 '12 at 20:03
i want to assume that elsewhere means a hotel not in the resort. so a customer can either book resort based accommodation or a room hotel, however a resort can also have hotels in them. im going to model it – D.G Mar 7 '12 at 20:09
@JoseDavidGarciaLlanos: If I understand you correctly, this above model would still work: You will only need two rows in LocationTypes; one for "Resort" one for "Off-resort". If you want to simplify the model, you could remove LocationTypes and then replace Location.LocationTypeID with a simple "Is a Resort?" indicator field. – FrustratedWithFormsDesigner Mar 7 '12 at 20:13
If i use hotel as an accommodation type, it is very unlikely that a customer will book many hotels, however a customer can book a whole chalet, bungalow. how can i deal with this, thats if a customer decides to book a room in a hotel – D.G Mar 7 '12 at 21:49

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.