MySQL – Sakila Structure

MySQL has a sample database which is useful for tutorials, samples and a reference for testing integration scenarios. The scripts to create the sakila sample schema put tables, views, procedures, triggers and data in place.

The sample also includes the sakila.mwb file which is a MySQL Workbench data model that you can open within MySQL Workbench to examine the database structure. For more information, see MySQL Workbench.

Data model of sakila sample database

Not all MySQL data types are used in the sample schema but it does cover the most common as well as a BLOB for a picture and GEOMETRY for location data. Here’s a description of each table in a convenience text structure which is searchable.

describe actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe actor_info;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id   | smallint unsigned | NO   |     | 0       |       |
| first_name | varchar(45)       | NO   |     | NULL    |       |
| last_name  | varchar(45)       | NO   |     | NULL    |       |
| film_info  | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
describe address;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| address_id  | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| address     | varchar(50)       | NO   |     | NULL              |                                               |
| address2    | varchar(50)       | YES  |     | NULL              |                                               |
| district    | varchar(20)       | NO   |     | NULL              |                                               |
| city_id     | smallint unsigned | NO   | MUL | NULL              |                                               |
| postal_code | varchar(10)       | YES  |     | NULL              |                                               |
| phone       | varchar(20)       | NO   |     | NULL              |                                               |
| location    | geometry          | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe category;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type             | Null | Key | Default           | Extra                                         |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| category_id | tinyint unsigned | NO   | PRI | NULL              | auto_increment                                |
| name        | varchar(25)      | NO   |     | NULL              |                                               |
| last_update | timestamp        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
describe city;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| city_id     | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| city        | varchar(50)       | NO   |     | NULL              |                                               |
| country_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe country;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| country_id  | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| country     | varchar(50)       | NO   |     | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| store_id    | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| email       | varchar(50)       | YES  |     | NULL              |                                               |
| address_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| active      | tinyint(1)        | NO   |     | 1                 |                                               |
| create_date | datetime          | NO   |     | NULL              |                                               |
| last_update | timestamp         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer_list;
+----------+-------------------+------+-----+---------+-------+
| Field    | Type              | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| ID       | smallint unsigned | NO   |     | 0       |       |
| name     | varchar(91)       | YES  |     | NULL    |       |
| address  | varchar(50)       | NO   |     | NULL    |       |
| zip code | varchar(10)       | YES  |     | NULL    |       |
| phone    | varchar(20)       | NO   |     | NULL    |       |
| city     | varchar(50)       | NO   |     | NULL    |       |
| country  | varchar(50)       | NO   |     | NULL    |       |
| notes    | varchar(6)        | NO   |     |         |       |
| SID      | tinyint unsigned  | NO   |     | NULL    |       |
+----------+-------------------+------+-----+---------+-------+
describe film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field                | Type                                                                | Null | Key | Default           | Extra                                         |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| film_id              | smallint unsigned                                                   | NO   | PRI | NULL              | auto_increment                                |
| title                | varchar(128)                                                        | NO   | MUL | NULL              |                                               |
| description          | text                                                                | YES  |     | NULL              |                                               |
| release_year         | year                                                                | YES  |     | NULL              |                                               |
| language_id          | tinyint unsigned                                                    | NO   | MUL | NULL              |                                               |
| original_language_id | tinyint unsigned                                                    | YES  | MUL | NULL              |                                               |
| rental_duration      | tinyint unsigned                                                    | NO   |     | 3                 |                                               |
| rental_rate          | decimal(4,2)                                                        | NO   |     | 4.99              |                                               |
| length               | smallint unsigned                                                   | YES  |     | NULL              |                                               |
| replacement_cost     | decimal(5,2)                                                        | NO   |     | 19.99             |                                               |
| rating               | enum('G','PG','PG-13','R','NC-17')                                  | YES  |     | G                 |                                               |
| special_features     | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES  |     | NULL              |                                               |
| last_update          | timestamp                                                           | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
describe film_actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              |                                               |
| film_id     | smallint unsigned | NO   | PRI | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe film_category;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| film_id     | smallint unsigned | NO   | PRI | NULL              |                                               |
| category_id | tinyint unsigned  | NO   | PRI | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe film_list;
+-------------+------------------------------------+------+-----+---------+-------+
| Field       | Type                               | Null | Key | Default | Extra |
+-------------+------------------------------------+------+-----+---------+-------+
| FID         | smallint unsigned                  | YES  |     | 0       |       |
| title       | varchar(128)                       | YES  |     | NULL    |       |
| description | text                               | YES  |     | NULL    |       |
| category    | varchar(25)                        | NO   |     | NULL    |       |
| price       | decimal(4,2)                       | YES  |     | 4.99    |       |
| length      | smallint unsigned                  | YES  |     | NULL    |       |
| rating      | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
| actors      | text                               | YES  |     | NULL    |       |
+-------------+------------------------------------+------+-----+---------+-------+
describe film_text;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| film_id     | smallint     | NO   | PRI | NULL    |       |
| title       | varchar(255) | NO   | MUL | NULL    |       |
| description | text         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
describe inventory;
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type               | Null | Key | Default           | Extra                                         |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| inventory_id | mediumint unsigned | NO   | PRI | NULL              | auto_increment                                |
| film_id      | smallint unsigned  | NO   | MUL | NULL              |                                               |
| store_id     | tinyint unsigned   | NO   | MUL | NULL              |                                               |
| last_update  | timestamp          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
describe language;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type             | Null | Key | Default           | Extra                                         |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| language_id | tinyint unsigned | NO   | PRI | NULL              | auto_increment                                |
| name        | char(20)         | NO   |     | NULL              |                                               |
| last_update | timestamp        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
describe nicer_but_slower_film_list;
+-------------+------------------------------------+------+-----+---------+-------+
| Field       | Type                               | Null | Key | Default | Extra |
+-------------+------------------------------------+------+-----+---------+-------+
| FID         | smallint unsigned                  | YES  |     | 0       |       |
| title       | varchar(128)                       | YES  |     | NULL    |       |
| description | text                               | YES  |     | NULL    |       |
| category    | varchar(25)                        | NO   |     | NULL    |       |
| price       | decimal(4,2)                       | YES  |     | 4.99    |       |
| length      | smallint unsigned                  | YES  |     | NULL    |       |
| rating      | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
| actors      | text                               | YES  |     | NULL    |       |
+-------------+------------------------------------+------+-----+---------+-------+
describe payment;
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type              | Null | Key | Default           | Extra                                         |
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| payment_id   | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| customer_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| staff_id     | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| rental_id    | int               | YES  | MUL | NULL              |                                               |
| amount       | decimal(5,2)      | NO   |     | NULL              |                                               |
| payment_date | datetime          | NO   |     | NULL              |                                               |
| last_update  | timestamp         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe rental;
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type               | Null | Key | Default           | Extra                                         |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| rental_id    | int                | NO   | PRI | NULL              | auto_increment                                |
| rental_date  | datetime           | NO   | MUL | NULL              |                                               |
| inventory_id | mediumint unsigned | NO   | MUL | NULL              |                                               |
| customer_id  | smallint unsigned  | NO   | MUL | NULL              |                                               |
| return_date  | datetime           | YES  |     | NULL              |                                               |
| staff_id     | tinyint unsigned   | NO   | MUL | NULL              |                                               |
| last_update  | timestamp          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
describe sales_by_film_category;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| category    | varchar(25)   | NO   |     | NULL    |       |
| total_sales | decimal(27,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
describe sales_by_store;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| store       | varchar(101)  | YES  |     | NULL    |       |
| manager     | varchar(91)   | YES  |     | NULL    |       |
| total_sales | decimal(27,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
describe staff;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| staff_id    | tinyint unsigned  | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   |     | NULL              |                                               |
| address_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| picture     | blob              | YES  |     | NULL              |                                               |
| email       | varchar(50)       | YES  |     | NULL              |                                               |
| store_id    | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| active      | tinyint(1)        | NO   |     | 1                 |                                               |
| username    | varchar(16)       | NO   |     | NULL              |                                               |
| password    | varchar(40)       | YES  |     | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff_list;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID       | tinyint unsigned | NO   |     | 0       |       |
| name     | varchar(91)      | YES  |     | NULL    |       |
| address  | varchar(50)      | NO   |     | NULL    |       |
| zip code | varchar(10)      | YES  |     | NULL    |       |
| phone    | varchar(20)      | NO   |     | NULL    |       |
| city     | varchar(50)      | NO   |     | NULL    |       |
| country  | varchar(50)      | NO   |     | NULL    |       |
| SID      | tinyint unsigned | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
describe store;
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field            | Type              | Null | Key | Default           | Extra                                         |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| store_id         | tinyint unsigned  | NO   | PRI | NULL              | auto_increment                                |
| manager_staff_id | tinyint unsigned  | NO   | UNI | NULL              |                                               |
| address_id       | smallint unsigned | NO   | MUL | NULL              |                                               |
| last_update      | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+

That’s it. All the tables and views in the sakila sample database described.

Notice that sakila does not cover all MySQL data types such as TINYBLOB, VARBINARY etc. The next blog post will examine how the MySQL Connector/J JDBC driver represents that data and introduce a new table for testing all the supported data types.

One thought on “MySQL – Sakila Structure

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s