- database solutions and downloads for microsoft access

Designer for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available

Second Normal Form (2NF) - Normalising Your Database:

Now we've looked at normalising a database to 1NF (First Normal Form), we will continue to investigate normalising to Second Normal Form.

A table is in first normal form and each non-key field is functionally dependent on the entire primary key.

Look for values that occur multiple times in a non-key field. This tells you that you have too many fields in a single table.

A Practical Approach

In the example below, see all the repeating values in the name and ProjectTitle fields. This is an inefficient way to store and maintain data. In a well-designed database, the only data that is duplicated is in key fields used to connect tables. The presumption is that the data in key fields will rarely change (so it's OK if it's repeated) while the data in non-key fields may change frequently (so it's not OK to repeat it).

A table with a multi-field primary key and repeating data in non-key fields
*EmployeeID LastName FirstName *ProjectNumber ProjectTitle
EN1-26 O'Brien Sean 30-452-T3 STAR manual
EN1-26 O'Brien Sean 30-457-T3 ISO procedures
EN1-26 O'Brien Sean 31-124-T3 Employee handbook
EN1-33 Guya Amy 30-452-T3 STAR manual
EN1-33 Guya Amy 30-482-TC Web Site
EN1-33 Guya Amy 31-241-TC New catalog
EN1-35 Baranco Steven 30-452-T3 STAR manual
EN1-35 Baranco Steven 31-238-TC STAR prototype
EN1-36 Roslyn Elizabeth 35-152-TC STAR pricing
EN1-38 Schaaf Carol 36-272-TC Order system
EN1-40 Wing Alexandra 31-238-TC STAR prototype
EN1-40 Wing Alexandra 31-241-TC New catalog

If a ProjectTitle changed, you would have to edit it in several records. And what would happen in this table if the EmployeeID was part of the primary key and you wanted to add a new ProjectNum and ProjectTitle even though no employees had yet been assigned?

The primary key cannot contain a null value so you couldn't add the new project. Additionally, if a project ended and you wanted to delete it, you would have to delete the individual values because, if you deleted the records containing the titles and an employee was assigned to only that project, you would also delete that employee's record - something that you may not want to do.

In the above example, the asterisks indicate the fields that make up the primary key of this table as it now stands. A multi-field primary key is necessary because neither the EmployeeID nor the ProjectNum fields contain unique values.

The reason there are repeated values in LastName, FirstName, and ProjectTitle is that these fields are dependent on only part of the primary key. The value in EmployeeID determines what the value in LastName will be but the value in ProjectNum has nothing to do with it. Similarly, the value in ProjectNum determines the value in ProjectTitle but EmployeeID does not. These non-key fields relate to only part of the primary key. They are not functionally dependent on the entire primary key.

The solution to this lies in breaking the table into smaller tables that do meet second normal form. You will find that more tables is the solution to most problems encountered during data normalisation.

Complying with second normal form

Now we'll take the table above and design new tables that will eliminate the repeated data in the non-key fields.

  1. To decide what fields belong together in a table, think about which field determines the values in other fields. Create a table for those fields and enter the sample data.
  2. Think about what the primary key for each table would be and about the relationship between the tables. If necessary, add foreign keys or a junction table.
  3. Mark the primary key for each table and make sure that you don't have repeating data in non-key fields.


*EmployeeID Last Name First Name
EN1-26 O'Brien Sean
EN1-33 Guya Amy
EN1-35 Baranco Steven
EN1-36 Roslyn Elizabeth
EN1-38 Schaaf Carol
EN1-40 Wing Alexandra


*EmployeeID *ProjectNum
EN1-26 30-452-T3
EN1-26 30-457-T3
EN1-26 31-124-T3
EN1-33 30-328-TC
EN1-33 30-452-T3
EN1-33 32-244-T3
EN1-35 30-452-T3
EN1-35 31-238-TC
EN1-36 35-152-TC
EN1-38 36-272-TC
EN1-40 31-238-TC
EN1-40 31-241-TC


*ProjectNum ProjectTitle
30-452-T3 STAR manual
30-457-T3 ISO procedures
30-482-TC Web site
31-124-T3 Employee handbook
31-238-TC STAR prototype
31-238-TC New catalog
35-152-TC STAR pricing
36-272-TC Order system

Examine the tables to make sure there are no repeating values in non-key fields and that the value in each non-key field is determined by the value(s) in the key field(s). This removes the modification anomaly of having the repeated values.

That's all for the second article on normal forms. Check the following article to view details of normalising your database to 3NF (3rd normal form)