The second normal form (2NF)
The second normal deals with data redundancy. The rules comprised in the second normal form are as follows.
- The database fullfills all the requirements of the first normal form
- All non-primary-key fields in a table must be fully dependent on the entire primary key of the table.
By inpecting your tables you can identify data that is not dependent on the primary key of the table. You can identify such data by looking which table columns repeat data. The com_type field in the ContactMoments table is such a column. The values "telephone", "email" and "letter" are repeated in the column. This is called data redundancy and it can be avoided by using two separate tables. Below are the new Contact moments table and the new CommunicationTypes table.
Contact moments
enlarge
CommunicationTypes
The communication_type field of the ContactMoments table is linked to the com_type_id field in the CommunicationTypes table. A one-to-many relationship exists between communication types and contact moments. Each communication type can be linked to multiple contact moment records. Vice versa, each contact moment is associated with one communication type. There are some advantages to working this way.
- You can always consult the communication types table and be absolutely sure how many and what type of communications are possible. In the two table model your application could query the CommunicationTypes table and be certain that all communication types are retrieved. Retrieving communication types from the ContactMoments table in the previous one table situation would yield all communication types that have occured so far. In the one table setup, the list containing all communication types would have to be stored in code somewhere.
As stated, if you don't store
communication types in a separate table, you would have to store them in code. This can be a data array in application code, or it could be a HTML dropdown menu
containing all possible values. Storing data in application code or HTML is bad practice. Imagine you have a site with contact forms at three different locations.
You would have to modify the communication type dropdown menu's for each contact form separately if you don't store the dropdown contents in the database.
- A person with administrative rights on the database can easily change, add or delete a communication type. Such an update would have to occur in only one location in the communication types table. In the old one table situation changing "e-mail" to "email" would require changing all the occurences of "e-mail" in the communication_type column of the ContactMoments table.
The same applies to the inbound_outbound column. The words "inbound" and "outbound" are stored redundantly. If you want to adhere to the second normal form, you could split off inbound_outbound into a separate table that could for example be called CommunicationDirection. However, in this database design tutorial I will leave the inbound_outbound field in tact. The chances you want to add a new communication direction in the future are probably slim. Perhaps "internal" could be added in the future to accomodate for the registration of contact moments between employees, but let's not make this tutorial too lengthy.
On the next page of this database design tutorial I will dig into the third normal form.