Vacation Rental Marketplace ER diagram and database schema

What this schema models

A complete vacation rental marketplace database design, built the way short-term rental platforms actually run: 24 tables, 17 enums and 40 relationships covering hosts and guests, identity checks and wishlists, listings with photos, amenities, house rules and versioned cancellation policies, a per-night availability calendar with pricing rules and iCal sync, instant and request-to-book bookings with per-night price snapshots, guest payments, refunds and host payouts, two-sided reviews and message threads. THE FOUR DESIGN RULES 1. One row per listing per NIGHT. calendar_nights is keyed on (listing_id, night), and a booking claims its nights with one conditional UPDATE that must touch exactly as many rows as nights booked. A night carries only one booking_id, so two overlapping bookings can never both commit. A night is the date you sleep, so check-out day stays free for the next check-in. 2. Prices are snapshotted per night. booking_nights stores each night's price and the rule that produced it, and every fee, discount and tax is a booking_charges line. Editing a weekend or seasonal rule never changes a confirmed stay, and "why was Saturday more?" has an answer. 3. Guest money and host money are separate. Guest payments follow the guest's schedule (pay part now, part later); host payouts are released about 24 hours after check-in. The platform holds the funds in between, so a cancellation before arrival is a refund to the guest, never a clawback from the host. 4. Reviews are blind until both are in. When the first review of a stay arrives, its published_at is set to the review deadline; when the second arrives, both are published at once. Readers filter on published_at <= now(), so neither side reads the other first and no job has to run at the deadline. DETAILS WORTH STEALING - Cancellation policies are versioned rows, never edited. A booking points at the exact version it was made under, and the cancellation stores the refund it computed. - Pricing resolves in a fixed order: calendar override, then the highest-priority seasonal or weekend rule, then the base price, with length-of-stay, early bird and last minute discounts applied to the subtotal. - Instant book and request to book share one bookings table; a request holds its nights and auto-declines after 24 hours. - Exact coordinates are shown only to confirmed guests, and messages sent before confirmation have contact details masked to keep payment on the platform. - iCal import and export feeds turn busy dates from other sites into blocked nights. - Hosts and guests are one users table, because most hosts travel too. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: users and trust, listings, availability and pricing, bookings, payments and payouts, and reviews and messaging. Every table carries a note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a vacation rental, holiday let or short-term booking marketplace, and for students who need a realistic vacation rental ER diagram for a DBMS project. This is a schema design reference; occupancy taxes, local short-term rental rules and payment regulation vary by market and belong in your own business logic.

Tables (24)

Relationships (40)

• booking_charges.booking_id references bookings.id many to one
• booking_nights.booking_id references bookings.id many to one
• booking_nights.pricing_rule_id references pricing_rules.id many to one
• bookings.cancellation_policy_id references cancellation_policies.id many to one
• bookings.guest_id references users.id many to one
• bookings.host_id references users.id many to one
• bookings.id references cancellations.booking_id one to one
• bookings.listing_id references listings.id many to one
• calendar_nights.booking_id references bookings.id many to one
• calendar_nights.listing_id references listings.id many to one
• calendar_syncs.listing_id references listings.id many to one
• cancellations.cancelled_by_user_id references users.id many to one
• guest_payments.booking_id references bookings.id many to one
• guest_payments.payment_method_id references payment_methods.id many to one
• host_payouts.booking_id references bookings.id many to one
• host_payouts.host_id references users.id many to one
• host_payouts.payout_account_id references payout_accounts.id many to one
• house_rules.listing_id references listings.id many to one
• identity_verifications.user_id references users.id many to one
• listing_amenities.amenity_id references amenities.id many to one
• listing_amenities.listing_id references listings.id many to one
• listing_photos.listing_id references listings.id many to one
• listings.cancellation_policy_id references cancellation_policies.id many to one
• listings.host_id references users.id many to one
• message_threads.booking_id references bookings.id many to one
• message_threads.guest_id references users.id many to one
• message_threads.host_id references users.id many to one
• message_threads.listing_id references listings.id many to one
• messages.sender_id references users.id many to one
• messages.thread_id references message_threads.id many to one
• payment_methods.user_id references users.id many to one
• payout_accounts.host_id references users.id many to one
• pricing_rules.listing_id references listings.id many to one
• refunds.booking_id references bookings.id many to one
• refunds.guest_payment_id references guest_payments.id many to one
• reviews.author_id references users.id many to one
• reviews.booking_id references bookings.id many to one
• reviews.subject_id references users.id many to one
• saved_listings.listing_id references listings.id many to one
• saved_listings.user_id references users.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Vacation Rental Marketplace database schema?

24 tables and 40 relationships, across 214 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.