How to Design a Database for AirBNB

[@DatabaseStar]

Bonus PDF: This ERD, descriptions of each table and column with sample data, and SQL scripts to create all of the tables on your own database: www.databasestar.com/dbdesign/?

[@endurancethemartian]

Wow what a video. I haven't seen someone explain DBA so eloquently.

[@DatabaseStar]

Thanks! Glad you liked it!

[@mehboob_h]

Amazing video. A great example of solving a real world database design problem. Excellent

[@DatabaseStar]

Glad it was helpful!

[@yudi6451]

Wow Amazing, i learn so much in 19minute and its using real world example. Keep this series going. Excellent

[@DatabaseStar]

Thanks! I'm glad it was helpful. I've started to do real-world examples in my database design videos, so it's good to hear it's been useful.

[@VincentCheng1658]

You're talented and dedicated one, Ben, glad to have you here!

[@DatabaseStar]

Thanks for the kind words!

[@shibinabraham7819]

You are truly a master at work and explanation. hats off to you!

[@DatabaseStar]

Wow, thank you!

[@shibinabraham7819]

Thank you for your response. For the benefit of others, would it be possible for you to explain how to use look up tales for joins (Using Key Value Pair)?@@DatabaseStar

[@DatabaseStar]

Good idea, I can make a video for that

[@shibinabraham7819]

@@DatabaseStar Thank you very much, looking forward to it. Especially the Key: Value Join(as a composite key) is a very tricky one. Please include that for sure.

[@lyricass7810]

I was waiting for this video from last 2 months thanks for uploading sir.

[@DatabaseStar]

Glad you like it!

[@lyricass7810]

​@@DatabaseStarhello sir, can you tell me how you made data dictionary for your airbnb database design? can you tell me which data dictionary tool is good for mysql, it would be nice if the data dictionary also has a column called "referenced table", please let me know sir, thank you..

[@sobeck6900]

Wow, such a fantastic video and way of presenting! You really stepped up your game with this one.

[@DatabaseStar]

Thanks a lot! I wanted to try a different approach, with an example, so I’m glad you like it.

[@sobeck6900]

@@DatabaseStar yea, this kind of approach when you crop the image for each step from real sites is really brilliant

[@sobeck6900]

@@DatabaseStarIt feels so 'real life' because, typically, when I need to design a database, I first receive the UI from Figma and then create ER diagrams based on that UI

[@fd4242]

Excellent. Thanks for making and sharing this video.

[@DatabaseStar]

No problem, glad it helped!

[@mohammadjaradat7885]

Amazing, One of most entertaining and educated video ,keep going 👍👍

[@DatabaseStar]

Thanks a lot!

[@muhammadsalmanagustian75]

Very informative and helpful. keep going Database Star!

[@DatabaseStar]

Thanks, will do!

[@shahnawazk]

Very informative and helpful. Thanks and keep it up!

[@DatabaseStar]

Thanks!

[@juliomuhlbauer]

I was looking for this some months ago. Amazing!

[@DatabaseStar]

Glad it helped!

[@lyricass7810]

hello sir, can you tell me how you made data dictionary for your airbnb database design? can you tell me which data dictionary tool is good for mysql, it would be nice if the data dictionary also has a column called "referenced table", please let me know sir, thank you.. another doubt of mine is, what should i do if i need to create a seperate table for host, what will change if i create a seperate table for host. if seperate table is created for host, what tables should it be linked to ? what are the new tables that might arise due to seperate table for host.

[@DatabaseStar]

I don't think I understand your first question. How did I make a data dictionary for an AirBNB design? This video shows exactly how I did it. I used a tool called LucidChart for this video, which I think is pretty good. But you can use Visio or Google Drawings or the built-in modelling tool in MySQL Workbench.

[@DatabaseStar]

If you want to create a separate table for host, you would have to work out what a host is related to, and how. A host would be related to a property. They may also have reviews (if users can review the host).

[@lyricass7810]

@@DatabaseStar sorry i forgot that you used lucid chart lol sorry sir.

[@Sdirimohamedsalah]

Great tutorial! I'm working on the same topic with a little bit more specificity. Honestly, my brain feels fried trying to find a good solution to make it translatable. Do you have any ideas aside from adding postfix fields like description_En, description_FR, etc.? Is there a better approach for solving this problem and translate the entier content of a database ?

[@DatabaseStar]

Glad it was helpful! I think it would be better to have a separate table for the descriptions in each language, as there can be many of them. Perhaps there's a new table called description_intl (or another name that you think is better) and it can have a description ID as the PK, an ID as the FK to the main table, and then a column for the language, and then the text of the description.

[@maneaabdullah6059]

Amazing, keep going 👍👍

[@DatabaseStar]

Thanks!

[@haidertest1809]

Hi Ben, Great and straight to the point video as always. Can you please make a video for a MySQLdatabase design for a user and vendor relationship application as following. Vendor can have multiple orders from users. User can book multiple vendors. User can leave reviews for each vendor etc. I would like to see to your approach on that.

[@DatabaseStar]

Thanks! Sure, I can make a video on that.

[@haidertest1809]

Thanks a Ton@@DatabaseStar

[@abhinaysadineni1151]

Can also let us know to store the nightly price per each date in the database? I have seen this feature in airbnb

[@DatabaseStar]

Yeah you could expand this to have different prices for different dates or date ranges.

[@reuelcazaubon]

What books would you recommend for database design?

[@DatabaseStar]

The one that comes to mind is Database Design for Mere Mortals. I've started reading it but haven't finished it yet.

[@reuelcazaubon]

@@DatabaseStar I started as well but never finished.

[@Code-sz9db]

Noice vid! In the video you kind of removed the image table by mistake. It's not included in the pdf too. Thanks for the video!

[@DatabaseStar]

Good pickup! It should be there, I don't know why I removed it.

[@sujaritnrc]

I live your tutorial. Real world example. If you have time pls share how to design noSQL like Firebase.

[@DatabaseStar]

Thanks! Good idea.

[@Dev_dkap]

What about property availability? Where is that captured?

[@DatabaseStar]

The availability can be calculated by working out the dates where there are no bookings - using the checkin_date and checkout_date of a property.

[@hhgforfhuv]

Nice and helpful video. Thanks a lot. Can you make video about designing the database of sports aggregator application like flashscore? I think it will be interesting

[@DatabaseStar]

Thanks! Sure I can do that. I’ve never heard of flashscore but I can look into it.

[@hhgforfhuv]

@@DatabaseStar will be great! Thanks

[@Im_Ashwin.M]

Very helpful videos. Can you make video about airline PNR database. A PNR can have 1. passenger name (first name, last name, title) 2. flight details (airlineCode, flight number, startCity, endCity, startTime, endTime) 3. phone (cityCode, number) 4. email 5. remarks 6. Special service requests (requestCode, status, remark)

[@DatabaseStar]

Good idea!

[@recapMate]

Why having the total_price field in bookings table when u can compute it?

[@DatabaseStar]

Good point! Generally I don't add computed fields to this table, but I did for this instance because it's a value at a point in time and includes a range of other values that could change over time. But you could also not store it and compute it instead.

[@LekkyDev]

Property image got removed. why?

[@DatabaseStar]

What do you mean the property image got removed?

[@LekkyDev]

@@DatabaseStar Sorry i meant image table. @3:28 there was an image table but no more there at the end.

[@nigelnyakudya2262]

beautiful content, make one for business like shopee.

[@DatabaseStar]

Thanks! I have one for an ecommerce store where I used ASOS.

[@nigelnyakudya2262]

@DatabaseStar link please

[@AndrulisTravel]

Nice

[@DatabaseStar]

Thanks!