How to i model this data

database-designerd

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

Best Answer

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",...)
Related Question