The Web, Untangled


◀ JQuery Table of contents PHP ▶

MySQL

Create your tables

Because we are creating a database for films, it makes sense to have a film table. For now, our film table will have attributes for:

  • title: the title of the film
  • genre: the film's genre (action, suspense, documentary, etc)
  • running length: the film's run time in minutes

Also, every table will have an ID column. This is very important, is it will be used as a primary key for each piece of data that's put into the database. A primary key is a piece of data that uniquely identifies each row (piece of data) in a database table.

When creating a database table, we have to think carefully about the datatypes for each database column. The ID column must always be an integer (INT in MySQL), and it needs to be unique for each row. To ensure this, we can add some constraints:

  • AUTO_INCREMENT: This will automatically add 1 to every new row in the table
  • NOT NULL: This makes sure that the data has some value, which is important for the ID column as every row must have a unique ID as its primary key!

The other columns in our film table are more open to interpretation. We use VARCHAR(255) as the datatypes for both the film's title and genre. This is the equivalent of a string type in other languages, meaning that it is just a piece of text. The 255 in VARCHAR(255) is the maximum size of the string in bytes. Don't worry about this too much, just remember that 255 is the usual number to use for this. The VAR in VARCHAR means that the text's length is variable (i.e. it doesn't always have to be 255 bytes in size). Again, don't worry about this too much - we'll cover standard (non-variable-length) CHAR types (and why you would use them) later.

The final column in our table is running_length, which will be the length of a movie in minutes and can just be an integer (INT in MySQL).

Here's how to create a table with the above constraints in MySQL:

CREATE TABLE film (
  id INT AUTO_INCREMENT NOT NULL,
  title VARCHAR(255),
  genre VARCHAR(255),
  running_length int,
  PRIMARY KEY (id)
)

The final line that says PRIMARY KEY (id) states that the ID column is the primary key - the column that will uniquely identify each row of the database. Be careful to put the name of the column (id) inside parentheses! The parentheses are there in case you want to put multiple column names to form a compound primary key.

OK, so now we've created our film table. What next? How about adding some directors?

CREATE TABLE director (
  id INT AUTO_INCREMENT NOT NULL,
  surname VARCHAR(255),
  firstname VARCHAR(255),
  date_of_birth DATE
  PRIMARY KEY (id)
)

The structure of this director table is much the same as for the film table above, but with one difference: we've added a date_of_birth column for the director's birthday. This is a datatype that expects a date of the format YYYY-MM-DD (year, month, day), such as "1985-12-31". We'll explain this further when we start inserting rows into the database.

Time to add one more table, this time for actors. For this one, we'll keep the same columns we used for the director table, but add some INT columns for height and weight, and an ENUM for the director's sex. In this case, we want to represent their sex as "m" for male and "f" for female, and accept no other inputs. This is what we use ENUM types for. By putting the type as ENUM('m', 'f'), we are stating that we will only accept "m" or "f" as the input here.

CREATE TABLE actor (
  id INT AUTO_INCREMENT NOT NULL,
  surname VARCHAR(255),
  firstname VARCHAR(255),
  sex ENUM('m', 'f'),
  height_cm INT,
  weight_kg INT,
  date_of_birth DATE
  PRIMARY KEY (id)
)

OK, so we've created our tables! Nice work. Time to start linking them together. For that, we need to use foreign keys.

Foreign keys

For the first example, we want to add a director to the film table, so that each film has a director associated with it. To do this, we need to add a foreign key to the table. A foreign key is an integer that matches the unique ID (the primary key) of a row in another table.

At the moment, our film table doesn't have a foreign key for directors. We'll have to add it using the ALTER TABLE command:

ALTER TABLE film ADD COLUMN director_id INT;

This adds a column called director_id to the film table, with an integer datatype. It's a good idea to put _id at the end of a foreign key name to remind yourself that it's just an integer that matches the ID in another table. Also note that the COLUMN keyword in ADD COLUMN is optional - the command works just fine without it.

Now that we've added that column, we need to tell our database that it's a foreign key to the director table:

ALTER TABLE film ADD FOREIGN KEY (director_id) REFERENCES director(id);

Here, we tell the database that the numbers in the director_id column all refer to id numbers in the director table. Again note that the name of the key (director_id) goes inside parentheses!

A film doesn't just have a director though. It also has actors that star in it! To avoid creating a many-to-many relationship (for now), we are going to limit the information about our films to one actor per film, with a lead_actor column.

Rather than altering the table again to add this column, I'll use this opportunity to show you how to add foreign keys when you create the actual table. So let's delete the table and start again:

DROP TABLE film;

The DROP TABLE command deletes a table from your database. So be careful when you use it!

Now, let's add our film table back in, but this time with director_id and lead_actor_id as foreign keys:

CREATE TABLE film (
  id INT AUTO_INCREMENT NOT NULL,
  title VARCHAR(255),
  genre VARCHAR(255),
  running_length int,
  director_id INT,
  lead_actor_id INT,
  FOREIGN KEY (director_id) REFERENCES director(id),
  FOREIGN KEY (lead_actor_id) REFERENCES actor(id),
  PRIMARY KEY (id)
)

Now we have a new films table that's linked to both the director and actor tables, using the director_id and lead_actor_id foreign keys.

Insert data into your tables

The time has come to actually start adding data into our database. Let's start by adding a couple of actors:

INSERT INTO actor VALUES (NULL, "Lamarr", "Hedy", "f", 145, 45, "1914-11-09");
INSERT INTO actor VALUES (NULL, "Kinsky", "Klaus", "m", 156, 55, "1933-12-23");

Here, we've added two new actors into the actor table. The comma-separated values that come after the VALUES keyword correspond to the columns of the table in order. This is including the id column. Because the id value is auto-generated whenever we add a new row to the table, we've entered its value as NULL. To avoid having to do this, and to enter details of actors whose height and weight we don't know, we can specify the columns that we're entering data for:

INSERT INTO actor (surname, firstname, sex)
VALUES ("Fonda", "Henry", "m");

INSERT INTO actor (surname, firstname, sex, date_of_birth)
VALUES ("Bergman", "Ingrid", "f", "1915-08-29");

Great. So now we have a reasonable amount of actors in our database. Time to add some directors. I'm going to use the second approach (specifying the columns we're adding data to) to do this, just as a matter of preference. You can use whichever approach you prefer.

INSERT INTO director (surname, firstname) VALUES ("Hitchcock", "Alfred");
INSERT INTO director (surname, firstname) VALUES ("Coppola", "Sofia");
INSERT INTO director (surname, firstname) VALUES ("Lynch", "David");
INSERT INTO director (surname, firstname) VALUES ("Frankenheimer", "John");

Each of these actors and directors that we've added have been assigned automatically-generated IDs. They should be incremental, starting with 1 and ending with the number corresponding to however many items you've added. For example, actor Hedy Lamarr will have an ID of 1, and Ingrid Bergman will have an id of 2. For the directors, Sofia Coppola's ID will be 2, and David Lynch's is 3.

We have to use these IDs when adding our films to the database, as they must be used as the IDs for the foreign keys for director_id and lead_actor_id. Let's add some films now. I'm going to create one called "Once Upon a Time in Twin Peaks", starring Henry Fonda and directed by David Lynch:

INSERT INTO film (title, genre, running_length, director_id, lead_actor_id)
VALUES ("Once Upon a Time in Twin Peaks", "Western", 200, 3, 1);

Note the IDs we've used at the end: David Lynch's ID is 3 (as we added him to the director table third) and Henry Fond's is 1 (as he was first in the actor table).

Let's try another example: "Lost in Casablanca", starring Ingrid Bergman (whose actor ID is 2) and directed by Sofia Coppola (whose ID is also 2):

INSERT INTO film (title, genre, running_length, director_id, lead_actor_id)
VALUES ("Lost in Casablanca", "Film noir", 180, 2, 2);

Now both films are linked to their respective directors and lead actors through the director_id and lead_actor_id foreign keys.