Db Design of a Room Booking System

If you only had two tables, Session and Booking, you could do this:

1. Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
2. Add CourseSizeMax column, and BookingNumber column to your dbo.Booking table.
3. Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
4. Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
5. Add a UNIQUE constraint on dbo.Booking(CourseId, BookingNumber)

and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.

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. :-)