- 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
The final database design
The final database design is shown belown in an image that was obtained from Microsoft Access 2003.
In the database, clients are linked to contact moments in a one to many relationship, using the customer_id fields as primary and foreign keys. That is why these fields are connected by a line. The symbols on both end of the line show you what type of relation exists between these tables. For each customer (1) there can be 0, 1 or an infinite number of contact moments (?) and vice versa, each contact moment is linked to exactly 1 customer. ? is a mathematic symbol that indicates infinity.
The CommunicationTypes and ContactMoments table are linked in a one to many relationship as well. Each (1) row in the CommunicationTypes table can be linked to multiple (?) rows in the ContactMoments table and each row in the ContactMoments table is linked to exactly 1 row in the CommunicationTypes table.
This database is normalized to the second normal form. A transitive relationship exists between the postal code and the city fields. In my country, the Netherlands, the city can be derived from the postal code. In this case I will leave this transitive relationship in place. Removing it would require the creation of a separate table that maps postal codes to cities. Creating such a table for an entire country requires a large amount of work, or the investment in a commercial postal code table.