Full database schema for an Airbnb-like short-term rental marketplace featuring host and guest profiles, property listings with amenities and photos, availability calendars, dynamic pricing, booking lifecycle management, bidirectional reviews, messaging, wish lists, and host payout tracking.
This schema models a two-sided rental marketplace: the same person can list a property one week and book someone else's the next. That is why there is no separate hosts table — host_profiles hangs off users and holds only what is true of someone acting as a host, such as superhost status and response rate.
The booking side is built around the idea that a price is a property of a date, not of a listing. listings carries a base_price, but availability_calendar overrides it per night, which is what lets a weekend or a holiday cost more without duplicating the listing.
Money is recorded twice on purpose. bookings stores what the guest paid, broken into base_total, cleaning_fee and service_fee; payouts stores what the host received. They are different numbers because the platform keeps a cut, and keeping both means a payout can be reconciled against the booking that produced it.
A stay produces a guest review and a host review, and they are written at different times, become visible under different rules, and can be reported separately. Holding both in one row means every query has to know which half it is reading, and a single row cannot be published twice. Two rows with a type column costs one extra join and makes 'show me everything this user wrote' a plain filter.
A night that is blocked has no price, and a night that costs more is usually a night that is in demand. Splitting them into two tables means two writes to change one night and a join to read it. One row per listing per date keeps the calendar a single scan, and the table stays small because only dates that differ from the default need a row.
listing_photos.position exists because the cover photo is a product decision, not a timestamp. Relying on created_at means reordering requires rewriting rows, and any change to upload flow silently reshuffles the gallery.
A message thread often starts before a booking exists — someone asking whether the place allows dogs. listing_id is set from the beginning, booking_id is filled in if the enquiry turns into a stay. Requiring a booking up front would mean either blocking pre-booking questions or creating placeholder bookings.