Normalization: Should I do this in 2NF or 3NF?

Question

Consider a database storing the data of invoices, and each invoice takes the following form:

Invoice_Num: 1480
Handled By: John Keaton
Staff Num: 263
Staff Position: Trainee

Item_Num    Item_Desc         Unit_Price   Qty
========    ==============    ==========   ===
 15003      Blue Ballpen         2.00       5
 15012      5ml Gluestick        0.80       3

Assumptions:

• Each invoice is handled by 1 and only 1 staff.
• Each item in the same invoice is unique.

So, in 1NF, the data can be represented as:

Invoice [ Invoice_Num, Staff_Num, Staff_Name, Staff_Pos, Item_Num, Item_Desc, Unit_Price, Qty ]

where Invoice_Num and Item_Num form the primary key.

Now we know that Invoice_Num -> Staff_Num, and Staff_Num -> Staff_Pos, and Staff_Num -> Staff_Name.

But then when the database is normalized to 2NF, should we move the Staff_Pos and Staff_Name to another table? If we use the transitive property, we have Invoice_Num -> Staff_Pos, and Invoice_Num -> Staff_Name, then we should move them to another table to eliminate partial dependency. However, with the use of transitive property, should these 2 dependencies be considered transitive dependency instead, and hence moving Staff_Pos and Staff_Name to another table should be done in 3NF?

Answer 1

If's a design choice, and you have to consider the trade-off:

If you use 2NF, there will be some duplication, which can potentially be detrimental to integrity - you can change a value in one place and not in the other.

So, if you decide to go with 2NF, you have to be more careful about integrity.

If you use 3NF, there will be no duplication. But you might end up with too many tables, and have hard time keeping track of them.