Database Design for a Hotel Management System

[@DatabaseStar]

Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?

[@thomasleclech2917]

Nice video! I have a database suggestion: a movie theatre reservation system with movie choice, seat booking, options (popcorn, etc) :)

[@DatabaseStar]

That's a great idea!

[@wimeatsworld]

This single video goes more in depth than a lot of videos I've found on this here KZbin. The most appreciated differentiator is that you're actually explaining the reasoning vs just dropping the answer. That said, I'm curious to the reasoning in Requirement 6. You're creating a table for the bed types, which I get. Is the reason you're not creating one for the number of beds that that number would mirror the id? Since it's a finite number of number of beds and a many to many relationship as (strictly speaking) all the configurations are possible. In other words: are you applying the many to many options with bed numbers through the room_class_bed_type table as another table for the num_beds would just clutter the ERD?

[@DatabaseStar]

Thanks for the kind words! Good to know what you appreciate about the video. Yes, I think that's one of the reasons: we don't need another table for the num_beds. The num_beds field in room_class_bed_type would have values like 1, 2, 3, and up to the maximum number of beds you can have in a single hotel room.

[@andriifadieiev9757]

Pure awesomeness. Just as always!

[@DatabaseStar]

Thanks!

[@hassaankhan4949]

Fantastic video! Just a heads up, it seems there might be a small oversight in "booking" table as the room_id wasn't removed after establishing many-to-many relationship. Incredibly informative and helpful, thank you!

[@DatabaseStar]

Oh thanks for letting me know! Glad you liked the video!

[@goodsamaritan208]

Hi Ben, I really enjoy your videos. Can you help me out with a doubt I have - What is the best way to update the "room_status" when a booking is made? I am assuming the "room_status" table has to be updated in a transaction along with "booking" table to prevent race conditions eg if someone else is trying to book the same room at the same time. Is this to be in application/server or are there better way to handle this in the DBMS itself?

[@DatabaseStar]

Good question! Yes you could include it in a transaction to ensure another transaction doesn't claim it. Or you could have a status of "booking in progress" for a particular room, meaning the room is currently on hold while the booking is being made. I'm not sure what the major booking sites do, but I think as a user we are able to put the room on hold (or a concert ticket using TicketMaster for example) while we book it.

[@kashmirshadows8150]

Thank you for the video

[@DatabaseStar]

You're welcome!

[@sigalnotovich8159]

great video , but you need to add primary key for the booking_room table for the booking id and room id combination so that no duplicates will appear in this table and less bug whould be produced in a real world application.

[@DatabaseStar]

Good point! You can add a primary key to ensure unique values, either as a new column or the combination of these two columns. You could also add Unique Constraint on both the booking_id and room_id columns, so that the combination of those two columns must be unique, to avoid bugs as you mentioned.

[@Alfenijonas]

Nice and neat. Subscribed!

[@DatabaseStar]

Thanks for the sub!

[@YacineBenjedidia-wm6pw]

Very interesting thanks. Can you please explain to us the relation between the databases and the applications itself in the real world.

[@DatabaseStar]

Thanks! What do you mean by the relation? As in, how applications use the databases, or something else?

[@YacineBenjedidia-wm6pw]

@@DatabaseStar yes how applications use the databases like this hotel database design

[@mhaesh]

Nice video. Here how to check room availability in particular date or date range. In room table we don't have relation ship with date with availability , Can you please any one help me on this. Thanks in advance.

[@DatabaseStar]

Thanks! I think you could find the dates that the room is booked, and then use that to find the opposite - the dates the rooms are not booked.

[@ShreeharshaV]

Great video. Looks like room_id FK needs to be removed from booking table because we have booking_room table for that. Booking can have multiple rooms and room is associated with multiple bookings. Pls check and let me know. Thanks

[@DatabaseStar]

Yes, you're right, booking.room_id needs to be removed. It's shown as an FK in the diagram but it doesn't relate to anything.

[@ajdevul]

This video will gain more viewership since it’s touching fundamentals, Great pace and explanation. Just a question on PKs though, why should we not chose the business attributes here for PKs? Is it a better practice to opt system generators? Thanks in advance.

[@DatabaseStar]

Thanks! It's possible to use the business attributes but from what I've seen, most people advise using system generated IDs (which is what I recommend as well). This is because you can ensure they never change and are unique. Something that has a business relevance, such as a Social Security Number or phone number can change or be reused (even if it's rare). You can still add constraints if certain values or combinations need to be unique.

[@SushantBhadkamkar]

Perhaps I missed this in the other vides on this channel, but why is having a separate table preferred over defining an enum column for life cycle concepts like "room_status"?

[@DatabaseStar]

Good question. I have created a video on this concept here: kzbin.info/www/bejne/eZy1k5p6eZydhK8 Generally, I recommend using lookup tables instead of enums because they are easy to update (it's easier to update or insert rows in a table than run an Alter Table statement to change an enum), you can easily populate drop-down lists with the available options, and can add extra context to the values (such as the order to display them in, active/inactive, effective dates). Hope this helps.

[@3kkrm]

7:18 Here it is clarified that the number of beds in each room is restricted to the room class. What I mean is that if we have a deluxe room and it has 1 king bed, there cannot be another deluxe room that has a different number of beds. And if I didn’t understand this well i want u to help me out with it

[@DatabaseStar]

Good question. You can have multiple room types with different numbers of beds: the deluxe with 1 King would be one row, and the deluxe with 2 Kings would be another row. You can have multiple rooms with the same class and number of beds. At least that's how I think it works - perhaps the database design is wrong here.

[@3kkrm]

@@DatabaseStar alright then i will make a small edit in here , and thank u very very very much your video helped me so much

[@tempMahad]

Could you please make for a Point of Sale with sessions(shifts) which are closed and count off the money generated

[@DatabaseStar]

Sure, I can do that!

[@tempMahad]

@@DatabaseStar will be waiting because I'm working on a project but I have been recreating database almost every day

[@bink6873]

that's great tutorial but i have small a question : how to display default attributes in ERD? As my research, there's no regulation about that. It's great if you can help me. Thanks for reading

[@DatabaseStar]

I don't know if there's a standard way to do it, but you could add some text or a comment to the diagram near the column to indicate the default values.

[@ItachiUchiha-i4r]

I have a question, how to query for availability of rooms between checkin date and checkout date ? I guess we will have to do joins. If yes, can it be avoided?

[@DatabaseStar]

You could query the booking, booking_room, and room tables to see which rooms are available. The WHERE clause would need to filter on booking.checkin_date and booking.checkout_date do not overlap with the user's selected checkin and checkout date. Yes, you would have to do joins, but this is a good thing.

[@reuelcazaubon]

Keep up the good work

[@DatabaseStar]

Thanks, will do!

[@XOXOLOLO10]

wow , thank you soooooooooooooooooooo much

[@DatabaseStar]

No problem!

[@taslimsuman]

Really love it

[@DatabaseStar]

Thanks!

[@gauravsrivastava17]

The pdf is so helpful

[@DatabaseStar]

Glad you like it!

[@recapMate]

Hello 😊 Please, why rooms and bookings are a many to many? I cant imagine where a room can belong to multiple bookings, as no different guests are to share the same room

[@DatabaseStar]

A room can have many bookings in its lifetime (one booking for this week, and another for next week), and a booking can be for many rooms (a family booking two rooms). The same room would not be booked at the same time by a different guest. I imagine there would be some application feature to prevent a currently booked room from being booked.

[@AustinDaniels]

Could we possibly get a ChatGPT clone database design video soon? 😅

[@DatabaseStar]

Good idea, but I don’t know a lot about ChatGPT to design a database for it.

[@gc5035]

Hi, please make a generalised video on this question. How do you design a database for each part of application have different access?(DB design for Section permission of Application Ex:Amazon) admin can create similar admins only. admin can create users . users can not create other users. admin can assign rules to different types of users and also we will have custom attributes property (read, write, Both,Edit)

[@DatabaseStar]

Thanks for the idea, I can make a video on that!

[@MohammedAbdelkrimGuendouz]

كلامك صحيح 100%

[@DatabaseStar]

Thanks!

[@Chris-by8eh]

Not sseeing the SQL scripts in your site

[@DatabaseStar]

They should all be on my GitHub repo here: github.com/bbrumm/databasestar

[@reuelcazaubon]

what if payment method was to be capture

[@DatabaseStar]

You could add this as a lookup table and link to one of the other tables.

[@mrhhh693]

How can I improve my skills to design such db?

[@DatabaseStar]

I think practice is the best way. You can come up with a scenario or a type of system you want to design for, then list out what you want the database to do, then create tables and columns for each one, like I do with my videos.

[@mrhhh693]

@@DatabaseStar where can I find scenarios to practice?

[@GUENDOUZ796]

Where is Requirement pdf

[@DatabaseStar]

You can get the PDF for this video here: www.databasestar.com/dbdesign/

[@Atish_abdi]

i need pdf for resources where i dowload

[@DatabaseStar]

You can get them at the link mentioned in the video and description: www.databasestar.com/dbdesign/