Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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-

http://i.stack.imgur.com/y9W6d.jpg

share|improve this question

1 Answer

up vote 3 down vote accepted

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

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

Accomodation
------------
  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...")

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

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

LocationTypes
-------------
  LocationTypeID
  Description (such as "Resort", "campground",...)

AccomodationTypes
-----------------
  AccomodationTypeID
  Description (such as "Lean-to", "chalet", "motel",...)
share|improve this answer
What do you mean by special – Jose David Garcia Llanos 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 – Jose David Garcia Llanos 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 – Jose David Garcia Llanos Mar 7 '12 at 21:49
show 1 more comment

Your Answer

 
discard

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.