Design a Database Schema From a Plain Description of Your App

You are a database designer turning a plain-English application description into a clean relational schema — normalised where it pays, practical where it doesn't.

<context>
[THE APPLICATION — what it does, in your words — e.g. "we hire out party equipment: customers book items for date ranges, pay deposits, gear gets damaged sometimes"]
[THE NOUNS AND RULES — the things and the rules you know: "an item can be booked by many customers over time but only once per date range", "an invoice can cover several bookings"]
[DATABASE — e.g. "Postgres", "MySQL", "SQLite"]
[EXPECTED SCALE — e.g. "hundreds of bookings a month"]
[QUERIES THAT MUST BE EASY — the questions the app asks constantly — e.g. "what's available on date X?"]
</context>

Before designing, extract the entities and — more importantly — the relationship cardinalities and lifecycle rules hiding in my description (can a booking exist without a customer? what happens to bookings when an item is retired?). List every rule you inferred, marked INFERRED — wrong inferences here become painful migrations later.

<task>
1. Present the schema: each table with columns, types for MY database, primary key, and one line on what a row represents. Model many-to-manys with junction tables; state the naming convention used.
2. Put the integrity rules in the schema, not the app: foreign keys with the right ON DELETE behaviour per lifecycle rule (justify each), NOT NULL, UNIQUE and CHECK constraints for the business rules I stated, and sensible defaults.
3. Flag deliberate denormalisation or its absence: where a purist would split further, and why you did or didn't at my stated scale.
4. Design for my named queries: the indexes each needs beyond the keys, and — for the one query in my list that's harder than it looks (date-range availability usually is) — the actual SQL against this schema.
5. Provide the complete CREATE script for my database, runnable as pasted, tables ordered so it executes cleanly.
6. Pressure-test it: walk 2 realistic scenarios from my description through the schema step by step (a booking with a damage charge; a cancellation), showing which rows appear and change — any awkwardness discovered means fix the schema now, and show the fix.
7. End with the [CONFIRM] list: every INFERRED rule the design depends on, as questions for me to verify against reality.
</task>

<output_format>
Inferred rules — schema tables — integrity decisions — indexes plus the hard query's SQL — CREATE script — scenario walkthroughs — [CONFIRM] questions.
</output_format>

Rules:
- Model only what my description supports; speculative tables ("you might want multi-currency later") get one line at the end, not a table.
- Types and syntax valid for the stated database; portability notes only where they cost nothing.
- If my description contains a genuine contradiction, surface it as the first [CONFIRM] question rather than designing around a guess.

Copy the block above straight into Claude — anything in [BRACKETS] is yours to fill in.