ErdDocs

Example schemas / Sakila Database

Sakila Database ER diagram

What is the Sakila database?

The Sakila database is MySQL’s official sample database, initially developed by Mike Hillyer of the MySQL AB documentation team. Its 16 tables model a DVD rental business: films and actors, physical copies in stores, customers, rentals and payments. It is small enough to read in one sitting. Yet it has a circular foreign key, a many-to-many with its own timestamp, and a table kept in sync by triggers instead of a key.

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

  • filmThe catalog entry: title, rating, rental terms, and two keys into language (spoken and original).
  • inventoryOne row per physical copy of a film in a store. Rentals point here, not at film.
  • rentalWho took which copy, when, and when it came back. return_date stays NULL while it is out.
  • paymentMoney received, by customer and staff member, linked to the rental it paid for when there was one.
  • customerA renter with a home store and an address. The active flag marks lapsed accounts without deleting them.
  • staff / storeEach store has one manager on its staff, and each staff member works at a store. That is the cycle.
  • actor / film_actorThe cast: a pure many-to-many between actors and films.
  • category / film_categoryGenres, also many-to-many, so one film can be both Comedy and Family.
  • address / city / countryA three-level geography shared by customers, staff and stores.
  • languageThe lookup film uses twice.
  • film_textTitle and description copied out of film for full-text search.

Design decisions worth copying

  • staff.store_id and store.manager_staff_id point at each other. A cycle like this cannot be loaded table by table, because one of the keys must wait. The official MySQL script turns foreign key checks off while loading. The SQL here creates both tables first and adds the second key with ALTER TABLE.
  • rental references inventory, not film. Renting is something done to a copy, so the copy is what the rental row names. “Which film” is one join away. Modeling it against film would lose which copy went out, and with it any way to tell what is still on the shelf.
  • film_text has no foreign key at all. It duplicates title and description so the search index can live on its own table. Three triggers on film (insert, update, delete) keep it in step. On the diagram it floats unconnected, because nothing but the triggers ties it to film.
  • payment.rental_id is nullable and set to NULL when the rental is deleted. A payment is a record of money, and money received does not vanish because the rental it settled was removed.

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.

CREATE TYPE mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

CREATE TABLE country (
    country_id   serial      PRIMARY KEY,
    country      varchar(50) NOT NULL,
    last_update  timestamp   NOT NULL DEFAULT now()
);

CREATE TABLE city (
    city_id      serial      PRIMARY KEY,
    city         varchar(50) NOT NULL,
    country_id   integer     NOT NULL REFERENCES country(country_id),
    last_update  timestamp   NOT NULL DEFAULT now()
);

CREATE TABLE address (
    address_id   serial      PRIMARY KEY,
    address      varchar(50) NOT NULL,
    address2     varchar(50),
    district     varchar(20) NOT NULL,
    city_id      integer     NOT NULL REFERENCES city(city_id),
    postal_code  varchar(10),
    phone        varchar(20) NOT NULL,
    last_update  timestamp   NOT NULL DEFAULT now()
);

CREATE TABLE language (
    language_id  serial    PRIMARY KEY,
    name         char(20)  NOT NULL,
    last_update  timestamp NOT NULL DEFAULT now()
);

CREATE TABLE category (
    category_id  serial      PRIMARY KEY,
    name         varchar(25) NOT NULL,
    last_update  timestamp   NOT NULL DEFAULT now()
);

CREATE TABLE actor (
    actor_id     serial      PRIMARY KEY,
    first_name   varchar(45) NOT NULL,
    last_name    varchar(45) NOT NULL,
    last_update  timestamp   NOT NULL DEFAULT now()
);

CREATE TABLE film (
    film_id               serial       PRIMARY KEY,
    title                 varchar(255) NOT NULL,
    description           text,
    release_year          smallint,
    language_id           integer      NOT NULL REFERENCES language(language_id),
    original_language_id  integer      REFERENCES language(language_id),
    rental_duration       smallint     NOT NULL DEFAULT 3,
    rental_rate           numeric(4,2) NOT NULL DEFAULT 4.99,
    length                smallint,
    replacement_cost      numeric(5,2) NOT NULL DEFAULT 19.99,
    rating                mpaa_rating  DEFAULT 'G',
    special_features      text,
    last_update           timestamp    NOT NULL DEFAULT now()
);
COMMENT ON COLUMN film.original_language_id IS 'Second foreign key into language: the language a dubbed film was made in';
COMMENT ON COLUMN film.rental_duration IS 'Days a rental lasts before it is late';
COMMENT ON COLUMN film.special_features IS 'MySQL keeps this as a SET of four values; one comma-separated text here';

CREATE TABLE film_text (
    film_id      integer      PRIMARY KEY,
    title        varchar(255) NOT NULL,
    description  text
);
COMMENT ON TABLE film_text IS 'Copy of film titles and descriptions for full-text search, kept in sync by triggers on film — no foreign key';

CREATE TABLE film_actor (
    actor_id     integer   NOT NULL REFERENCES actor(actor_id),
    film_id      integer   NOT NULL REFERENCES film(film_id),
    last_update  timestamp NOT NULL DEFAULT now(),
    PRIMARY KEY (actor_id, film_id)
);

CREATE TABLE film_category (
    film_id      integer   NOT NULL REFERENCES film(film_id),
    category_id  integer   NOT NULL REFERENCES category(category_id),
    last_update  timestamp NOT NULL DEFAULT now(),
    PRIMARY KEY (film_id, category_id)
);

CREATE TABLE staff (
    staff_id     serial      PRIMARY KEY,
    first_name   varchar(45) NOT NULL,
    last_name    varchar(45) NOT NULL,
    address_id   integer     NOT NULL REFERENCES address(address_id),
    email        varchar(50),
    store_id     integer     NOT NULL,
    active       boolean     NOT NULL DEFAULT true,
    username     varchar(16) NOT NULL,
    password     varchar(40),
    last_update  timestamp   NOT NULL DEFAULT now()
);
COMMENT ON COLUMN staff.store_id IS 'The store this person works at; the key is added after store exists';

CREATE TABLE store (
    store_id          serial    PRIMARY KEY,
    manager_staff_id  integer   NOT NULL UNIQUE REFERENCES staff(staff_id),
    address_id        integer   NOT NULL REFERENCES address(address_id),
    last_update       timestamp NOT NULL DEFAULT now()
);
COMMENT ON COLUMN store.manager_staff_id IS 'Unique: one store per manager. staff.store_id points back — a cycle';

ALTER TABLE staff ADD FOREIGN KEY (store_id) REFERENCES store(store_id);

CREATE TABLE customer (
    customer_id  serial      PRIMARY KEY,
    store_id     integer     NOT NULL REFERENCES store(store_id),
    first_name   varchar(45) NOT NULL,
    last_name    varchar(45) NOT NULL,
    email        varchar(50),
    address_id   integer     NOT NULL REFERENCES address(address_id),
    active       boolean     NOT NULL DEFAULT true,
    create_date  timestamp   NOT NULL,
    last_update  timestamp   DEFAULT now()
);
COMMENT ON COLUMN customer.store_id IS 'The customer''s home store';

CREATE TABLE inventory (
    inventory_id  serial    PRIMARY KEY,
    film_id       integer   NOT NULL REFERENCES film(film_id),
    store_id      integer   NOT NULL REFERENCES store(store_id),
    last_update   timestamp NOT NULL DEFAULT now()
);
COMMENT ON TABLE inventory IS 'One row per physical copy of a film in a store';

CREATE TABLE rental (
    rental_id     serial    PRIMARY KEY,
    rental_date   timestamp NOT NULL,
    inventory_id  integer   NOT NULL REFERENCES inventory(inventory_id),
    customer_id   integer   NOT NULL REFERENCES customer(customer_id),
    return_date   timestamp,
    staff_id      integer   NOT NULL REFERENCES staff(staff_id),
    last_update   timestamp NOT NULL DEFAULT now(),
    UNIQUE (rental_date, inventory_id, customer_id)
);
COMMENT ON COLUMN rental.inventory_id IS 'The copy rented, not the film: the film is reached through inventory';
COMMENT ON COLUMN rental.return_date IS 'NULL while the copy is still out';

CREATE TABLE payment (
    payment_id    serial       PRIMARY KEY,
    customer_id   integer      NOT NULL REFERENCES customer(customer_id),
    staff_id      integer      NOT NULL REFERENCES staff(staff_id),
    rental_id     integer      REFERENCES rental(rental_id) ON DELETE SET NULL,
    amount        numeric(5,2) NOT NULL,
    payment_date  timestamp    NOT NULL,
    last_update   timestamp    DEFAULT now()
);
COMMENT ON COLUMN payment.rental_id IS 'Nullable, SET NULL on delete: a payment outlives the rental it paid for';

FAQ

Where do I download the Sakila database?

From dev.mysql.com, on the MySQL Example Databases page, as sakila-schema.sql and sakila-data.sql. Both are licensed under the New BSD license. The SQL on this page is a PostgreSQL rendition of the same 16 tables and keys, written for this diagram.

Is there a PostgreSQL version of Sakila?

Yes. Pagila is the community port, and the dvdrental sample used by many PostgreSQL tutorials is derived from Sakila as well. Pagila replaces film_text with a tsvector column on film and refreshes last_update with a trigger instead of MySQL’s ON UPDATE.

How many tables does the Sakila database have?

Sixteen, plus seven views (customer_list, film_list, sales_by_store and others), three stored procedures and three functions. Views hold no data of their own, so the diagram shows the sixteen tables.

More example schemas