Completion of ERD, uncertainties about generalization/specialization and primary keys

Question

I'm practicing making conceptual databases and I would like to know of I'm on the right track. I've been trying to create an ERD for a scenario as follows:

---

A hotel can take bookings for weddings, conferences and theme nights, as well as regular regular rooms. All bookings have a date, time, booking code, name of staff member that took the booking, deposit paid and payment method.

Weddings bookings record the name of bride and groom, as well as client info (full name, address, phone number and email), who is making the booking and the date of the wedding reception. Number of guests, food menu (3 different alternatives), if the reception is in the evening, if it's open bar and if a photographer is needed. There is two available suites to hold the weddings, Plaza or Ballroom.

Conference bookings require the same client info, as well as name and address of the organisation that holds the conference. Number if participants and which food menu (4 different choices) is desired is also recorded. A conference have access to the same venues as weddings, but can book more than one. There is also 5 smaller conference rooms that can be booked. These can hold 15-20 people, have 2 different layouts and some contain electronic facilities and some have tea/coffee facilities.

Theme nights record the same client details, the number of participants and have access to one of the two venues.

Clients can also book individual rooms, either single or double sized room and most have en-suite facilities. The same client details are stored for this booking as well.

---

What I have managed to model so far is the one below, am I on the right track here?

I know I am missing cardinality, mainly because I haven't figured out any nice way to do it in draw.io which I'm using at the moment.

I feel rather unsure on how to use the generalization/specialization (the arrows), is this done in a good way?

EDIT: I´ve updated the ERD with Primary keys but I'm not very sure I'm taking the right approach with it...

EDIT 2: I have made some changes to the ERD with some consideration on the answers for this questions. I know some of the issues raised still are there, the one I mainly would consider is combining Meeting_Room and Suite but I'm not sure how to do it while keeping the constraint of the meeting rooms only being available to the conferences.

One thing that got misinterpreted was the Booker entity due to a mistake from my part. It didn't refer to the staff member taking the booking, but the person that did the booking.

"Weddings bookings record the name of bride and groom, as well as client info (full name, address, phone number and email), who is taking making the booking and the date of the wedding reception."

I removed this entity and put it as a field referring to Person instead as the only relevant information about the person taking the booking is the name (for this scenario).

Answer 1

I think you're creating more tables than necessary.

First, why do you need a table for "bridal couple"? Why not just include the bride and groom fields in wedding_booking table? The only reason to break this out into a separate table would be if you expected to book multiple weddings for the same couple, which seems rather unlikely. If they're getting re-married, it's probably not a second wedding to each other.

Why have tables for "bride" and "groom"? All these do is serve as an extra stepping stone to get from bride and groom id's to their names. Why not just have wedding_booking have fields for bride_name_id and groom_name_id?

Why is name separate from client? Why not just put the name fields in the client table? Okay, you also use name for the employee who does the booking. Really you need an employee table and a client table, with each including a name. With just two fields making up the name, there's little gained by breaking this out into a separate table.

I don't see any reason for room_booking to be a separate table. Booking should simply point to a room.

"Plaza" and "Ballroom" are not fields in suite. They are two different records. Maybe there's "description" that can have the value of "plaza" or "ballroom".

If I understand your intent, every wedding_booking must be linked to a booking. So there's no need to have a client_id in wedding_booking as it's already in booking.

Likewise staff_member in booking appears to be the same thing as booker_id in wedding_booking. It belongs just once, in booking.

Participants, delegates, and guests all appear to be the same idea: how many people are attending this event. I'd combine these into a single field in event_booking.

If a conference can use either a meeting room or a suite, and possibly multiples, it gets awkward to break these into two tables. I'd combine meeting_room and suite into a single table. If weddings and theme nights can book meeting rooms, fine, add a flag in there that says which it is, and then suites are available to all three types of events while meeting rooms are available only to conferences.

Many database designers would challenge me on this, but: I'd seriously consider combining theme night, conference, and wedding into a single table and having a type flag that indicates which it is. Most of what you need to record for these events is the same: how many people are coming, what room or rooms are they using, who's name is it under. A few fields only apply to one or the other, like conferences probably don't have flowers. But if somebody organizing a conference said they wanted to have flowers, would you tell them they can't because it's not a wedding? A conference or a theme night could have an open bar, I would think. Etc. My point is, there's no point limiting the services your hotel can offer to just certain types of events because you think they are unlikely to be wanted at other types of events -- unless there's really some reason why you just can't do it, legal restrictions or something. Also, if you later come up with another type of event you might book -- a birthday party or a bar mitzvah or a politician's victory party or whatever -- you just add another type, you don't have to struggle with creating a whole new table. You can even toss in an "other" type to allow for it so you don't have to scramble to reprogram the first time it happens.

Well, that's probably enough comments for the first round. :-)

Answer 2

I think you are on the right track. There are few fundamental issues which I would like to point out though.

1. Why do you need separate entities for Room, Suite, Meeting_Room? They all can be clubbed together. Create a new entity , may be call it Location. Add a type where you can specify Room, Suite, Meeting_Room etc. This way you have the flexibility of adding new locations like Outdoor Garden, Terrace etc in future without much hassle.

2. I see you have an entity called Booker. I think Employee would be an appropriate name. Plus, I honestly see no benefit of having an entity called Name, Groom or Bride. In stead, you can create an entity called Person with all basic attributes like name, address, contactinfo etc. Specialize Person to Employee, Groom, Bride etc. And reference everywhere you need. And I have never seen a separate entity for Name ever. Unless you have a specific use case, there is no need.

3. You rightly did a specialization for Event and Themenight, Wedding, Conference. I would even add RoomRental as another case of an event. That would simplify things.

Let me know if this helps.

Answer 3

Your use of shared primary keys makes sense to me. I note especially that the three specialized variants of a Booking all share a primary key with the generalized Event_booking. This concept of shared primary key is generally quite useful. It enforces the one to one nature of the gen-spec relationship. It provides quick and easy joins between each spec table and the gen table. And it obviates the need for a type field in the gen table.

I wonder why Suite_ID has to be in both Event_Booking and Conference_Booking.

As to whether you are on the right track at the conceptual level, that is a question of whether your conceptual level agrees with the way things are done in the real world. You'll have to get input from subject matter experts, and it may even work differently in different hotel chains.

Note: moved from the duplicate to here.