- 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
Linking tables
Customer information is stored in the Customers table. Our database needs a way to store contact moments as well. When I started designing databases I had a tendency to try to store related data in one table. You might, for example, try to store contact moments in the Customers table, by adding a new column for every contact moment or something similar. Customers and contact moments form separate entities in our system. They are related in a 'one-to-many' relationship. This relation requires the use of two separate tables. One for customers and one for contact moments. We will therefor create a separate table for contact moments. (Data relationships will be discussed extensively in the following pages)
First, let's decide what information to store about a contact moment.
- contact_moment_id (primary key)
- customer_id (foreign key, links contact moment to customer
- date
- time
- description (a description of the contact moment, such as an email, a summary of a telephone call, etc)
- in_out (was it an incoming or outgoing letter, phone call, email, etc
- com_type (What type of communication? Possible values are 'telephone call', 'email', etc.
Below is a concept version of the ContactMoments table. Later in this database design tutorial we will have another look at this database table and we will make some adjustments.
ContactMoments table
click to enlarge
Each row of the ContactMoments table contains information about one unique contact moment. The primary key, contact_moment_id, uniquely identifies each contact moment in the table. Furthermore, each contact moment is linked to a customer from the Customers table via the customer_id in the second column. The customer_id field in the ContactMoments table is called a 'foreign key' field. It refers to the primary key of another table, in this case, the Customers table.
In the current database a computer program could use the customer_id field in the ContactMoments table to find all the contact moments of a particular customer. The contact_moment_id in the ContactMoments table can be used to find the customer that belongs to a particular contact moment.
Now that you've seen a database design example it is time to focus on the relationships that can exist between tables. The example we've discussed is a 'one-to-many' relationship. For each customer (one) in the Customers table there can exist multiple (many) contact moments in the ContactMoments table. Vice versa, each contact moment belongs to one unique customer. The relational model describes three types of relationships that can exist between data.
- The one-to-many relationship
- The many-to-many relationship
- The one-to-one relationship
The relationships that exist between data dictate how we design our database. Relationships will be discussed in the next three pages of this database design tutorial.