ErdDocs

Example schemas / Chinook Database

Chinook Database ER diagram

What is the Chinook database?

The Chinook database is an open sample database by Luis Rocha, built as a modern alternative to Northwind. Its 11 tables model a digital music store. Artists, albums and tracks are on one side, and customers, invoices and invoice lines on the other. The employees who support customers sit in between. The media data comes from a real iTunes library, and the same schema ships as a single script for SQLite, PostgreSQL, MySQL, SQL Server, Oracle and DB2.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

Hover a table to light up its relations, and click to pin them.

What each table does

  • artist / album / trackThe catalog, three levels deep: an artist has albums, an album has tracks.
  • genre / media_typeThe two lookups every track points at.
  • playlist / playlist_trackPlaylists, and the pure many-to-many that fills them with tracks.
  • customerA buyer, with support_rep_id naming the employee who looks after them.
  • invoice / invoice_lineThe sale and its lines. Each line is a track at the price paid.
  • employeeStaff, with reports_to building the org chart inside the table.

Design decisions worth copying

  • customer.support_rep_id and employee.reports_to both point at employee, and neither says so by name. Declared keys draw both lines on this diagram. A tool guessing from names would see neither. It is the clearest small case for declaring foreign keys.
  • invoice_line.unit_price repeats track.unit_price on purpose. The track’s price is today’s, and the invoice line’s is what was paid. Joining back to track for history would reprice every old sale the day the catalog changes.
  • invoice copies the billing address from the customer rather than referencing it. An invoice is a document: when the customer moves, last year’s invoices must still say where they were sent.
  • track.album_id is nullable while track.media_type_id is not. The schema states that a file always has a format but need not belong to an album.

The SQL

PostgreSQL dialect. Paste it into the tool above (it is already there), into your own database, or into a migration. You are free to copy it.

Chinook Database ER diagram: 11 tables and 11 relations, drawn by ErdDocs from the SQL below.Chinook Database ER diagram: 11 tables and 11 relations, drawn by ErdDocs from the SQL below.
The diagram ErdDocs generates from this SQL.
CREATE TABLE artist (
    artist_id  integer      PRIMARY KEY,
    name       varchar(120)
);

CREATE TABLE album (
    album_id   integer      PRIMARY KEY,
    title      varchar(160) NOT NULL,
    artist_id  integer      NOT NULL REFERENCES artist(artist_id)
);

CREATE TABLE genre (
    genre_id  integer      PRIMARY KEY,
    name      varchar(120)
);

CREATE TABLE media_type (
    media_type_id  integer      PRIMARY KEY,
    name           varchar(120)
);
COMMENT ON TABLE media_type IS 'File formats: MPEG audio, AAC, protected and purchased variants';

CREATE TABLE track (
    track_id       integer       PRIMARY KEY,
    name           varchar(200)  NOT NULL,
    album_id       integer       REFERENCES album(album_id),
    media_type_id  integer       NOT NULL REFERENCES media_type(media_type_id),
    genre_id       integer       REFERENCES genre(genre_id),
    composer       varchar(220),
    milliseconds   integer       NOT NULL,
    bytes          integer,
    unit_price     numeric(10,2) NOT NULL
);
COMMENT ON COLUMN track.album_id IS 'Nullable: a track can exist outside any album';
COMMENT ON COLUMN track.unit_price IS 'The catalog price today; invoice_line keeps the price paid';

CREATE TABLE playlist (
    playlist_id  integer      PRIMARY KEY,
    name         varchar(120)
);

CREATE TABLE playlist_track (
    playlist_id  integer NOT NULL REFERENCES playlist(playlist_id),
    track_id     integer NOT NULL REFERENCES track(track_id),
    PRIMARY KEY (playlist_id, track_id)
);

CREATE TABLE employee (
    employee_id  integer     PRIMARY KEY,
    last_name    varchar(20) NOT NULL,
    first_name   varchar(20) NOT NULL,
    title        varchar(30),
    reports_to   integer     REFERENCES employee(employee_id),
    birth_date   timestamp,
    hire_date    timestamp,
    city         varchar(40),
    country      varchar(40),
    email        varchar(60)
);
COMMENT ON COLUMN employee.reports_to IS 'Self-reference: the manager, NULL for the general manager';

CREATE TABLE customer (
    customer_id     integer     PRIMARY KEY,
    first_name      varchar(40) NOT NULL,
    last_name       varchar(20) NOT NULL,
    company         varchar(80),
    city            varchar(40),
    country         varchar(40),
    email           varchar(60) NOT NULL,
    support_rep_id  integer     REFERENCES employee(employee_id)
);
COMMENT ON COLUMN customer.support_rep_id IS 'The employee who looks after this customer — a foreign key not named employee_id';

CREATE TABLE invoice (
    invoice_id       integer       PRIMARY KEY,
    customer_id      integer       NOT NULL REFERENCES customer(customer_id),
    invoice_date     timestamp     NOT NULL,
    billing_city     varchar(40),
    billing_country  varchar(40),
    total            numeric(10,2) NOT NULL
);
COMMENT ON COLUMN invoice.billing_country IS 'Copied from the customer at sale time, so later moves do not rewrite old invoices';

CREATE TABLE invoice_line (
    invoice_line_id  integer       PRIMARY KEY,
    invoice_id       integer       NOT NULL REFERENCES invoice(invoice_id),
    track_id         integer       NOT NULL REFERENCES track(track_id),
    unit_price       numeric(10,2) NOT NULL,
    quantity         integer       NOT NULL
);

FAQ

Where do I download the Chinook database?

From the lerocha/chinook-database repository on GitHub, licensed under MIT. It has one script per database, including a ready Chinook_Sqlite.sql that many SQL courses use. The SQL on this page is a PostgreSQL rendition of the same 11 tables and keys, with the longer address columns trimmed for reading.

How many tables are in the Chinook database?

Eleven: artist, album, track, genre, media_type, playlist, playlist_track, customer, invoice, invoice_line and employee. All of them appear on the diagram above, with every foreign key between them.

Why is it called Chinook?

It is named after Northwind, which it was meant to replace. A chinook is a warm wind in the interior West of North America, most often felt in southern Alberta. The schema is newer than Northwind, with a digital store instead of a food trader.

More example schemas