Sakila Database: table structure and schema overview
Sakila is a sample relational database designed by MySQL for learning and demonstrating SQL and relational database management system (RDBMS) capabilities.
This page presents Sakila table structure, key columns, and constraints commonly used in educational SQL queries.
The Sakila database contains 15 main tables describing various aspects of a DVD rental company.
ER diagram of the Sakila database
The list of tables
actor - actor table.
- actor_idunique record identifier (PK)
- first_nameactor's first name
- last_nameactor's last name
- last_updatedate and time of last update
| actor_id |
first_name |
last_name |
last_update |
| 1 |
John |
Doe |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (actor_id)
address - customer and staff addresses.
- address_idunique record identifier (PK)
- addresspostal address
- address2additional address
- districtdistrict or region
- city_idcity identifier (FK)
- postal_codepostal code
- phonephone number
- last_updatedate and time of last update
| address_id |
address |
address2 |
district |
city_id |
postal_code |
phone |
last_update |
| 1 |
123 Main St |
[null] |
Downtown |
1 |
12345 |
+1234567890 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (address_id)
- FOREIGN KEY (city_id) REFERENCES city(city_id)
category - film categories.
- category_idunique record identifier (PK)
- namecategory name
- last_updatedate and time of last update
| category_id |
name |
last_update |
| 1 |
Action |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (category_id)
city - city table.
- city_idunique record identifier (PK)
- citycity name
- country_idcountry identifier (FK)
- last_updatedate and time of last update
| city_id |
city |
country_id |
last_update |
| 1 |
Metropolis |
1 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (city_id)
- FOREIGN KEY (country_id) REFERENCES country(country_id)
country - country table.
- country_idunique record identifier (PK)
- countrycountry name
- last_updatedate and time of last update
| country_id |
country |
last_update |
| 1 |
United States |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (country_id)
customer - customer table.
- customer_idunique record identifier (PK)
- store_idstore identifier (FK)
- first_namecustomer's first name
- last_namecustomer's last name
- emailcustomer's email address
- address_idaddress identifier (FK)
- activecustomer activity indicator (0/1)
- create_datedate and time the customer was added to the database
- last_updatedate and time of last update
| customer_id |
store_id |
first_name |
last_name |
email |
address_id |
active |
create_date |
last_update |
| 1 |
1 |
John |
Doe |
john.doe@example.com |
1 |
1 |
2023-01-01 12:00:00 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (customer_id)
- FOREIGN KEY (store_id) REFERENCES store(store_id)
- FOREIGN KEY (address_id) REFERENCES address(address_id)
film - table of films.
- film_idunique record identifier (PK)
- titlefilm title
- descriptionbrief description or plot of the film
- release_yearyear the film was released
- language_ididentifier of the film's language (FK)
- original_language_ididentifier of the original language of the film in case it is dubbed into a new language
- rental_durationduration of rental period in days
- rental_ratecost of renting the film for the duration specified in the rental_duration column
- lengthlength of the film in minutes
- replacement_costamount of penalty for loss or damage of the disc
- ratingrating assigned to the film. Can be one of: G, PG, PG-13, R, or NC-17
- special_featureslist of special features included on the DVD. Can be zero or more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes
- last_updatedate and time of last update
| film_id |
title |
description |
release_year |
language_id |
original_language_id |
rental_duration |
rental_rate |
length |
replacement_cost |
rating |
special_features |
last_update |
| 1 |
Film Title |
A brief description of the film. |
2000 |
1 |
2 |
5 |
4.99 |
120 |
19.99 |
PG-13 |
Trailers, Commentaries |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (film_id)
- FOREIGN KEY (language_id) REFERENCES language(language_id)
- FOREIGN KEY (original_language_id) REFERENCES language(language_id)
film_actor - actors to films relation.
- actor_ididentifier for actor (FK)
- film_ididentifier for film (FK)
- last_updatedate and time of last update
| actor_id |
film_id |
last_update |
| 1 |
1 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (actor_id, film_id)
- FOREIGN KEY (actor_id) REFERENCES actor(actor_id)
- FOREIGN KEY (film_id) REFERENCES film(film_id)
film_category - films to categories relation.
- film_ididentifier for each film (FK)
- category_ididentifier for each category (FK)
- last_updatedate and time of last update
| film_id |
category_id |
last_update |
| 1 |
1 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (film_id, category_id)
- FOREIGN KEY (film_id) REFERENCES film(film_id)
- FOREIGN KEY (category_id) REFERENCES category(category_id)
inventory - table of items.
- inventory_idunique record identifier (PK)
- film_ididentifier of the film (FK)
- store_ididentifier of the store where the inventory is located (FK)
- last_updatedate and time of last update
| inventory_id |
film_id |
store_id |
last_update |
| 1 |
23 |
2 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (inventory_id)
- FOREIGN KEY (film_id) REFERENCES film(film_id)
- FOREIGN KEY (store_id) REFERENCES store(store_id)
language - films languages.
- language_idunique record identifier (PK)
- namelanguage name
- last_updatedate and time of last update
| language_id |
name |
last_update |
| 1 |
English |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (language_id)
payment - customers payments.
- payment_idunique identifier of the record (PK)
- customer_ididentifier of the customer (FK)
- staff_ididentifier of the staff member who received the payment (FK)
- rental_ididentifier of the rental record (FK)
- amountpayment amount
- payment_datedate and time of the payment
- last_updatedate and time of the last update
| payment_id |
customer_id |
staff_id |
rental_id |
amount |
payment_date |
last_update |
| 1 |
1 |
1 |
1 |
4.99 |
2023-01-01 12:13:14 |
2023-01-01 12:14:15 |
- PRIMARY KEY, btree (payment_id)
- FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
- FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
- FOREIGN KEY (rental_id) REFERENCES rental(rental_id)
rental - customers rentals.
- rental_idunique identifier of the record (PK)
- rental_daterental start date
- inventory_ididentifier of the disk (FK)
- customer_ididentifier of the customer (FK)
- return_datedate of returning the film
- staff_idid of the staff member issued the disk (FK)
- last_updatedate and time of the last update
| rental_id |
rental_date |
inventory_id |
customer_id |
return_date |
staff_id |
last_update |
| 1 |
2023-01-01 16:15:21 |
1 |
1 |
2023-01-10 09:12:36 |
1 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (rental_id)
- FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id)
- FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
- FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
staff - company staff.
- staff_idunique identifier of the record (PK)
- first_namefirst name of the staff member
- last_namelast name of the staff member
- address_ididentifier of the address (FK)
- picturephoto of the staff member
- emailemail address of the staff member
- store_idforeign key referencing the store table (FK)
- activeindicator of staff member's activity (0/1)
- usernameusername for system login
- passwordpassword for login
- last_updatedate and time of the last update
| staff_id |
first_name |
last_name |
address_id |
picture |
email |
store_id |
active |
username |
password |
last_update |
| 1 |
John |
Doe |
1 |
[null] |
john.doe@example.com |
1 |
1 |
johndoe |
******** |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (staff_id)
- FOREIGN KEY (address_id) REFERENCES address(address_id)
- FOREIGN KEY (store_id) REFERENCES store(store_id)
store - company stories.
- store_idunique identifier of the record (PK)
- manager_staff_ididentifier of the store manager (FK)
- address_ididentifier of the address (FK)
- last_updatedate and time of the last update
| store_id |
manager_staff_id |
address_id |
last_update |
| 1 |
1 |
1 |
2023-01-01 12:00:00 |
- PRIMARY KEY, btree (store_id)
- FOREIGN KEY (manager_staff_id) REFERENCES staff(staff_id)
- FOREIGN KEY (address_id) REFERENCES address(address_id)
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
If you’re wondering whether you should learn SQL, the answer is simple: absolutely.
SQL (Structured Query Language) is the language of data, and data powers every modern business. Whether you’re a data analyst, data scientist, business analyst, software engineer, financial analyst, product manager, or marketing professional, SQL helps you retrieve, analyze, and understand the information that drives decisions.