Keys are special pieces of data that help to identify a row of information in a table. In database terms, an individual row is also called a record and is composed of individual fields that correspond to columns (Figure 3.1).
Figure 3.1. The layout of a database table, with labels.
There are two types of keys you will deal with: primary and foreign. A primary key is a unique identifier that has to abide by certain rules. They must
The best real-world example of a primary key is the U.S. Social Security number. Although I have heard stories of duplicate numbers being assigned, the principle is that each individual has a unique Social Security number and that the number never changes. Just as the Social Security number is an artificial construct used to identify people, you'll frequently find creating an arbitrary primary key for each table to be the best design practice.
The second type of key is the foreign key. A foreign key is the representation in Table B of the primary key from Table A. If you have a hollywood database with a movies table and a directors table, the primary key from directors would be linked as a foreign key in movies. You'll see better how this works as the normalization process continues.
Currently, MySQL only enforces foreign keys when using the InnoDB storage engine (see Chapter 4, "Creating a MySQL Database," for more information on the different storage engines) but generally ignores their existence otherwise. Hence, foreign keys in MySQL are more of a theoretical presence than a binding one, although this should change in later versions of the software. But from a design perspective, you should keep in mind the correlation between primary and foreign keys.
The accounting database is just a simple table as it stands, but to start off the normalization process, I'll want to identify or create the primary key (the foreign keys will come in later steps).
To establish a primary key: