- 1Introduction
- 2Database history
- 3Relational database characteristics
- 4Customer contact system
- 5Tables and the primary key
- 6Linking tables
- 7The one-to-many relationship
- 8The many-to-many relationship
- 9The one-to-one relationship
- 10Database normalization
- 11The first normal form (1NF)
- 12The second normal form (2NF)
- 13The third normal form (3NF)
- 14The final database design
- 15Another example: webshop database
- 16Conclusion
Database design tutorial
Tables and the primary key
A relational database consists of tables. In a database table data are stored in rows. Earlier in this database design tutorial you saw an example of a table containing information about tutorials. The tutorial table consists of 6 rows or 'records' that each contain information about one specific tutorial. All 6 tutorials are different, but for each tutorial the same data items, namely tutorial_id, title and category, are stored in the table.
For the customer contact system we should create a table with customer information. Below is an example of a table that holds information about customers.
A computer program could try to select a customer in this table by searching for a customer's first name. That is not a good idea though. Since there are two Johns in the table it is impossible to select the John you want when searching only for the first name. Alternatively, you could search for the combination of the first and the last name. However, eventually a second customer named John Smith will become your customer. The main question here is 'how can we uniquely identify a table row?'
To uniquely identify each customer, each customer must be linked to a unique piece of information. This piece of information is called the primary key. In most cases the primary key is a number combination, but text values can be used too. For our customer contact system we will assign a unique customer ID to each customer. In our database design, the customer_id field will function as the primary key of the Customers table.
Customers table
More about the primary key
You may encounter primary keys in everyday life quite often.
- The order number you receive when you shop online is probably the primary key of some order table in the shop's database.
- Your social security number is probably a primary key in some government database.
- An invoice number could be used as a primary key in a database table that stores invoices that have been sent.
- A customer ID is used in many cases as a primary key in a customers table.
- A product number often refers to a product number primary key field in a 'products' table.
- etc.
What do these codes have in common?
- They are all unique numbers. Each customer, invoice, order, etc has it's own unique code.
- They are all used to locate specific data belonging to the primary key. (A customer_id is related to a customer's first name, last name, address, etc.)
As stated priviously, the primary key is a number or text value that must be unique. This means a primary key value can only occur once in the primary key column of a table. Relational database management systems are generally programmed to disallow the insertion of a duplicate primary key value. Trying to do so will result in a database error.
So, the primary key is used to uniquely identify table rows. Table rows correspond to occurences of a certain 'entity'. One row in a customers table represents one customer. One row in a orders table represents one order, etc. The primary key is also used to link data from multiple tables. The next page of this database design tutorial will show you how linking database tables works.