0

Hope this question is right here.

TL;DR

Database design for event places (venue and hotel), hotels can be venues.

Long Version

Introduction

I'm developing a software for trainings. Trainings are like a template with information and events show time and place where a specific training will happen. Events have a place where they happen, the venue. Events also have an (optional) hotel, where participants can book rooms. A hotel can also be a venue since they often have conference rooms. So if a hotel is venue all the contact information and address for the hotel and the venue are obviously the same.

Question

What is the best way to design the database for hotel and venue, so that a hotel can also be a venue.

Target of design:

  • avoid duplicate data if a hotel is a venue
  • easy to handle through my software

Possible solutions

  1. one table "Place" with columns "hotel_ID" and "venue_ID" which link to the tables "Hotel" and "Venue"

  2. one table "place" with an "isHotel" and "isVenue" column indicating which it is

  3. two separate tables "hotel" and "venue" and in hotel table a column "venueID" whch links to a venue?

    ... (something different)

0

2 Answers 2

2

avoid duplicate data if a hotel is a venue

Is this really a problem? If you have any available data, you should look to see how much duplication there really is. A hotel with convention space could have different names, billing addresses, contact information, etc. Also, a single hotel could have multiple meeting places with different names. Avoiding potential duplicate data could get difficult if you're trying to offer some flexibility. As far as data changes, how often will these places really change? If they relocate a hotel, you'd probably make a new entry anyway.

easy to handle through my software

How important is the question to the user, "Which venues are also a hotel?" You could get a way with a Venue table and Hotel table. There's always a Venue, so there could be a FK_Hotel_ID field for the optional link.

With 3 tables, you can have the shared data in the Place table, but again, you have to decide how much there really is. - Place (PK_PlaceID) - Hotel (FK_PlaceID) - Venue (FL_PlaceID)

It's much easier to eliminate the Place table in smaller apps and give up some reporting and data integrity benefits. Obviously with thousands of places there is a greater need to avoid duplication and make multiple data changes easier.

The Hotel and Venue tables could have data specific to them and optional over-riding data (billing address) of the Place table. The more you normalize your data like this, the trickier (beyond checking a boolean field) it becomes to determine what venues double as hotel.

If you're suggesting hotels for multiple venues (especially for large events where you run out of space) and need to indicate how far they are, you'll need some sort of Event table that handles the many-to-many relationship with venues and hotels.

0

You can have 3 entities. Address, Hotel and Venue. You store AddressId as FK in Hotel and Venue. So if your Venue and Hotel have same street address you dont have to enter Address again.

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