← All schemas
Schema Library

Chinook Database Schema Diagram

Chinook is a digital music store sample database: artists release albums, albums contain tracks, tracks have a genre and a media type, customers buy tracks through invoices and invoice lines, and tracks are grouped into playlists. All eleven tables and their foreign keys are mapped in the diagram above. Two details worth knowing: playlist_track is a composite-key join table with no surrogate id, and track.album_id and track.genre_id are nullable - a track can exist without an album (singles) or a genre, while media_type_id is always required.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Chinook database v1.4.5, Chinook_PostgreSql.sql (lerocha/chinook-database) · License: MIT

Tables
11
Core table
track
Sample data
15,000+ rows
Version
1.4.5

Chinook v1.4.5 PostgreSQL script by Luis Rocha.

Indexes, views, and the script's database-creation statements are omitted. Chinook_PostgreSql.sql.

Chinook database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/chinook">
  <img src="https://www.dbdiagramr.space/schemas/chinook-er-diagram.svg" alt="Chinook Database Schema Diagram (PostgreSQL)" width="1292" height="1019" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/chinook">Chinook Database Schema Diagram (PostgreSQL)</a> by dbdiagramr</p>

Tables in the Chinook schema

ColumnTypeNullableKey
album
album_idINTNoPK
titleVARCHAR(160)No-
artist_idINTNoFK → artist.artist_id
artist
artist_idINTNoPK
nameVARCHAR(120)Yes-
customer
customer_idINTNoPK
first_nameVARCHAR(40)No-
last_nameVARCHAR(20)No-
companyVARCHAR(80)Yes-
addressVARCHAR(70)Yes-
cityVARCHAR(40)Yes-
stateVARCHAR(40)Yes-
countryVARCHAR(40)Yes-
postal_codeVARCHAR(10)Yes-
phoneVARCHAR(24)Yes-
faxVARCHAR(24)Yes-
emailVARCHAR(60)No-
support_rep_idINTYesFK → employee.employee_id
employee
employee_idINTNoPK
last_nameVARCHAR(20)No-
first_nameVARCHAR(20)No-
titleVARCHAR(30)Yes-
reports_toINTYesFK → employee.employee_id
birth_dateTIMESTAMPYes-
hire_dateTIMESTAMPYes-
addressVARCHAR(70)Yes-
cityVARCHAR(40)Yes-
stateVARCHAR(40)Yes-
countryVARCHAR(40)Yes-
postal_codeVARCHAR(10)Yes-
phoneVARCHAR(24)Yes-
faxVARCHAR(24)Yes-
emailVARCHAR(60)Yes-
genre
genre_idINTNoPK
nameVARCHAR(120)Yes-
invoice
invoice_idINTNoPK
customer_idINTNoFK → customer.customer_id
invoice_dateTIMESTAMPNo-
billing_addressVARCHAR(70)Yes-
billing_cityVARCHAR(40)Yes-
billing_stateVARCHAR(40)Yes-
billing_countryVARCHAR(40)Yes-
billing_postal_codeVARCHAR(10)Yes-
totalNUMERIC(10,2)No-
invoice_line
invoice_line_idINTNoPK
invoice_idINTNoFK → invoice.invoice_id
track_idINTNoFK → track.track_id
unit_priceNUMERIC(10,2)No-
quantityINTNo-
media_type
media_type_idINTNoPK
nameVARCHAR(120)Yes-
playlist
playlist_idINTNoPK
nameVARCHAR(120)Yes-
playlist_track
playlist_idINTNoPK, FK → playlist.playlist_id
track_idINTNoPK, FK → track.track_id
track
track_idINTNoPK
nameVARCHAR(200)No-
album_idINTYesFK → album.album_id
media_type_idINTNoFK → media_type.media_type_id
genre_idINTYesFK → genre.genre_id
composerVARCHAR(220)Yes-
millisecondsINTNo-
bytesINTYes-
unit_priceNUMERIC(10,2)No-

Table relationships

10 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

One-to-many

album.artist_id → artist.artist_id

One artist can have many albums (album.artist_id -> artist.artist_id, required).

One-to-many

customer.support_rep_id → employee.employee_id

One employee can have many customers (customer.support_rep_id -> employee.employee_id, optional).

Self-referencing

employee.reports_to → employee.employee_id

One employee can have many child employees (employee.reports_to -> employee.employee_id, optional).

One-to-many

invoice.customer_id → customer.customer_id

One customer can have many invoices (invoice.customer_id -> customer.customer_id, required).

One-to-many

invoice_line.invoice_id → invoice.invoice_id

One invoice can have many invoice lines (invoice_line.invoice_id -> invoice.invoice_id, required).

One-to-many

invoice_line.track_id → track.track_id

One track can have many invoice lines (invoice_line.track_id -> track.track_id, required).

Many-to-many

playlist_track (playlist_id -> playlist.playlist_id, track_id -> track.track_id)

Many playlists can have many tracks through playlist_track (playlist_track.playlist_id -> playlist.playlist_id, playlist_track.track_id -> track.track_id).

One-to-many

track.album_id → album.album_id

One album can have many tracks (track.album_id -> album.album_id, optional).

One-to-many

track.genre_id → genre.genre_id

One genre can have many tracks (track.genre_id -> genre.genre_id, optional).

One-to-many

track.media_type_id → media_type.media_type_id

One media type can have many tracks (track.media_type_id -> media_type.media_type_id, required).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Top tracks by units sold

Rank tracks by invoice-line quantity.

select t.name, sum(il.quantity) as units
from track t
join invoice_line il on il.track_id = t.track_id
group by t.track_id, t.name
order by units desc, t.name;

Revenue by country

Sum invoice totals for each customer country.

select c.country, sum(i.total) as revenue
from invoice i
join customer c on c.customer_id = i.customer_id
group by c.country
order by revenue desc;

Sales by genre

Sum track sales grouped by genre.

select g.name, sum(il.unit_price * il.quantity) as revenue
from invoice_line il
join track t on t.track_id = il.track_id
join genre g on g.genre_id = t.genre_id
group by g.name
order by revenue desc;

Playlist sizes

Count tracks in each playlist.

select p.name, count(pt.track_id) as tracks
from playlist p
left join playlist_track pt on pt.playlist_id = p.playlist_id
group by p.playlist_id, p.name
order by tracks desc, p.name;

Frequently asked questions

Related schema diagrams

Visualize your own database

Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.

Try it free →