- 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
Database normalization
The guidelines for proper relational database design are laid out in the relational model. They are grouped into 5 groups called "normal forms". The first normal form represents the lowest form of database normalization, the fifth represents the highest form of database normalization. These normal forms are guidelines for good database design. You are not obliged to adhere to all five normal forms when designing a database. Nevertheless, you are advised to normalize you database to some extent, because normalization has some significant advantages in terms of the efficiency and maintainability of your database.
- In a normalized database structure you can make complex data selections with relatively simple SQL queries.
- Data integrity. A normalized database allows for reliable data storage. Most database management systems allow (or demand) you to enforce data integrity rules, such as entity integrity and referential integrity. I will skip the topic of data integrity in this database design tutorial. If you are interested you read more about data integrity rules at The University of Texas.
- Database normalization avoids duplicate (or redundant) storage of data. Data are always stored in only one location which makes it easy to insert, update or delete data. There is an important exception to this rule. As you have seen in the examples shown in this database design tutorial, keys are being stored in multiple locations. The primary key of table A is stored in a related table B as a foreign key. Actually only "logical data" are stored in only one place. Logical data are all non-key data. Bloor Research presents an interesting view on the performance penalties imposed by relational data storage (PDF) that discusses the duplication of key data in relational database design.
- Scalability. Scalability is the ability to meet with future growth demands. Scalability is a very important characteristic of any database model and for database management systems. The relational model is usually considered as a model that scales well, mainly because it avoids duplicate data storage. Database optimization and scalability are complex subjects and I am sure not everybody will agree with the view stated above. Discussing database scalability in depth is, however, a too large and complex subject to discuss in this database design tutorial.
These are some of the general tasks that are associated with database normalization.
- Ordering data in logical groups or sets.
- Finding relationships between sets of data. You have seen an examples of the one-to-many relationship (customers - contact moments) and the many-to-many relationship (beers - distributors) in this tutorial.
- Minimizing data redundancy. In other words, making sure logical data is stored in only one location.
Most databases don't adhere to all five normal forms presented in the relational model. Usually database are normalized to the second or third normal four. The fourth and fifth normal form are rarely used. I will therefor limit this database design tutorial to discuss only the first, second and third normal form.