advancedTop 15 Database Design Questions

Design a hotel room booking system that prevents double-bookings.

rooms(id, number, type, price_per_night) bookings(id, room_id FK, guest_id FK, stay_period DATERANGE NOT NULL, total_price, status) EXCLUDE constraint: EXCLUDE USING GIST (room_id WITH =, stay_period WITH &&) WHERE status != 'CANCELLED' GiST index automatically created by EXCLUDE constraint Partial index on active bookings: WHERE status IN ('CONFIRMED', 'PENDING') The EXCLUDE constraint prevents o

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to Design a social media platform database: users, posts, follows, likes, comments. →← Back to all SQL questions