The Guest List
Anyone can write three CREATE TABLEs. The hidden tests are the relational rules a vague schema forgets — the double-booking it allows, the orphans it leaves, the empty event it drops. SQLite enforces the constraints, not your intentions.
You are designing the database for a small events product. People sign up, browse events, and RSVP to the ones they want to attend. Each event has a name and a capacity — the maximum number of people who can attend. The product needs to know, for every event, how many people are coming. Your job is to write the schema: the tables, their columns, the relationships between them, and a view that reports attendance.
Model three things and one report. An events table holds each event's name and its capacity. A users table holds each person (identified by their email). An rsvps table records that a given user is attending a given event, with a status such as 'going'. Finally, a view named event_attendance lists every event alongside a count of how many people are attending it. Give each table an integer primary key named id.
Specify the schema completely — not just the shape of the data, but the rules it must hold. Think about what a real signup flow will throw at this database over time: the same person clicking RSVP twice, an organizer deleting an event, two people who typed their email with different capitalization, an event nobody signed up for, a popular event that fills past its capacity. Decide exactly how the schema should answer each of those, and write the constraints that make it so. The builder only adds the rules your spec names; a schema that just lists columns will ship every one of those bugs.
With an event 'Launch Party' (capacity 2) and users alice@corp.com and bob@corp.com, two 'going' RSVPs fill it, and event_attendance reports Launch Party with a count of 2. A third person attempting to RSVP, or alice RSVPing a second time, is where the rules start to matter.
- Tables events (name, capacity), users (email), and rsvps linking a user to an event.
- Each table has an integer primary key named id.
- A view event_attendance reporting a count of attendees per event.
The functional tests are shown, and the model usually clears them on its own. The hidden tests are the twists this kind of system is full of. They are not listed. Your spec only passes them if it already knows where this domain breaks.
166 chars