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.
Example
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.