4.1. The Basics of MySQL Data Storage
MySQL is a relational database management system, which lets you store data in multiple tables, where each table contains a set of named columns and each row consists of a data entry into the table. Tables will often contain information about other table entries, which allows developers to group relevant information into smaller groups to ease a script's load on the server, as well as simplifying data retrieval.
For example, take a look at how you might store information about musical artists (see Tables 4-1 and 4-2).
| artist_id | artist_name |
|---|---|
| 1 | Bon Iver |
| 2 | Feist |
| album_id | artist_id | album_name |
|---|---|---|
| 1 | 1 | For Emma, Forever Ago |
| 2 | 1 | Blood Bank - EP3 |
| 3 | 2 | Let It Die |
| 4 | 2 | The Reminder |
The first table, artists, includes two fields. The first field, artist_id, stores a unique numerical identifier for the artists. The second column, artist_name, stores the artist's name.
The second table, albums, stores a unique identifier for each album in the album_id column and the album name in the—you guessed it!—album_name column. The album table includes a third column, artist_id, that relates the artists and albums tables. This column stores the unique artist identifier that corresponds to the artist that recorded the album.