The address table

This article introduces the structure of the address table in the Sakila database.

The address table contains address information for customers, staff, and stores.

The customer, staff and store have foreign key that references the primary key of the address table.

Table Structure

The structure of the address table is as follows:

+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| 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 |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

Table Columns

The following table explains all columns in the address table.

Column Description
address_id The surrogate primary key is used to uniquely identify each address in the table.
address The first line of the address.
address2 The second line of address. It is Optional.
district The area where the address is located can be a state, province, county, etc.
city_id Foreign key, pointing to the city table.
postal_code The postal code of the address.
phone The phone number.
last_update The time when the row was created or last updated.
location Geometry data with spatial index.

Starting from MySQL 5.7.5, the spatial location column is supported. This column is only added when executing Sakila SQL files for MySQL server 5.7.5 and later. And, it also adds SPATIAL KEY idx_location.