AI Hints
Homeโ€บSQL Tutorialโ€บDesigning Databases
SQL ยท Lesson 79 of 126

Design Practice: Event Bookings

Two tables, one link.

โฑ 2 min readโ–ถ Video at 1:51:18๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen's events are popular. Now customers want to book seats. One event has many customers. And one customer can book many events. How should we store that?

Design Practice: Event Bookings
From the video: Design Practice: Event Bookings
  • Customers book seats
  • One event, many customers
  • One customer, many events

Many to many

This is called a many to many relationship. You can't put a list of customers inside an event row. That breaks rule one. Instead, you add a linking table. Each row in it says, this customer booked this event. Order items works exactly the same way, linking orders and books.

eventsone row per event
customersone row per customer
bookingsone row per booking
Likeorder_items

The design in SQL

Here's the design. Events, with rules on the title, date, and seats. And bookings, the linking table. Its two foreign keys point to events and customers. And its primary key is the pair of them together, so nobody can book the same event twice. Run it. Both tables now exist.

query.sqlsql
CREATE TABLE events (
    event_id   INTEGER PRIMARY KEY,
    title      TEXT NOT NULL,
    event_date TEXT NOT NULL,
    seats      INTEGER CHECK (seats > 0)
);
CREATE TABLE bookings (
    event_id    INTEGER REFERENCES events (event_id),
    customer_id INTEGER REFERENCES customers (customer_id),
    booked_on   TEXT,
    PRIMARY KEY (event_id, customer_id)
);
SELECT name FROM sqlite_master
WHERE name IN ('events', 'bookings');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   name   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ events   โ”‚
โ”‚ bookings โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Many to many relationships need a linking table, with two foreign keys. You just designed a real database. Next, how SQL differs between database products.

Key takeaways
  • Many to many
  • A linking table
  • Composite primary key
Next up: SQL Dialects โ€” SQLite, Postgres, MySQL, SQL Server