Wiki Home

Second Normal Form


Namespace: Wiki
A table is in 2nd NF when it is in First Normal Form and all non-key fields are dependent on the whole Primary Key for their value. You put a table into 2nd NF by moving any fields that are not dependent on the whole primary key to another table where they are dependent on the whole primary key.
EXAMPLE

Given an Invoice detail lines table with the following structure;
FieldMeaning
InvnoFK to invoice table and part of the PK
LinenoThe second part of the PK
ItemidFK to the inventory table
CustidFK to the customer table

Excepting the two components of the primary key, ask this question of the other fields, "Is each field dependent on the entire primary key for it value?" For the Itemid the answer is yes, but for the Custid you see that it is only dependent on part of the PK, that being the Invno, the customer does not change for subsequent lines.

The solution is to move the Custid field out of the detail lines table and put it in the Invoice table where it is dependent on the whole PK.
Category Data Modeling Data Normalization Third Normal Form
( Topic last updated: 2002.10.18 07:40:11 PM )