Software & Apps Apps 48 48 people found this article helpful Putting a Database in Third Normal Form (3NF) by Mike Chapple Writer Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. our editorial process Twitter Mike Chapple Updated on April 12, 2020 Westend61 / Getty Images Apps Best Apps Payment Services Tweet Share Email Third normal form (3NF) is a database principal that supports the integrity of data by building upon the database normalization principles provided by first normal form (1NF) and second normal form (2NF). The purpose of 3NF is to improve database processing while also minimizing storage costs. Third Normal Form Requirements There are two basic requirements for a database to be in 3NF: The database must already meet the requirements of both 1NF and 2NF.All database columns must depend on the primary key, meaning that any column's value can be derived from the primary key only. Primary Key Dependence Let's explore further what we mean by the fact that all columns must depend on the primary key. If a column's value can be derived from both the primary key and another column in the table, it violates 3NF. Consider a table of employees with these columns: EmployeeIDFirstNameLastName Do both LastName and FirstName depend only on the value of EmployeeID? Well, could LastName depend on FirstName? No, because nothing inherent in LastName would suggest the value of FirstName. Could FirstName depend on LastName? No again, because the same is true: whatever a LastName might be, it could not provide a hint as to the value of FirstName. Therefore, this table is 3NF compliant. But consider this Vehicles Table: VehicleIDManufacturerModel The Manufacturer and the Model could derive from the VehicleID, but the Model could also derive from the Manufacturer because only one particular manufacturer makes a vehicle model. This table design is non-3NF compliant, and could, therefore, result in data anomalies. For example, you might update the manufacturer without updating the model, introducing inaccuracies. Moving the additional dependent column to another table and referencing it using a foreign key would make it compliant. This would result in two tables: In the Vehicles Table below, the ModelID is a foreign key to the Models Table: VehicleIDManufacturerModelID This new Models Table maps models to manufacturers. If you want to update any vehicle information specific to a model, you would do it in this table, rather than in the Vehicles Table. ModelIDManufacturerModel Derived Fields in the 3NF Model A table might contain a derived field, which is one that is computed based on other columns in the table. For example, consider this table of widget orders: Order numberCustomer numberUnit priceQuantityTotal The total breaks 3NF compliance because it can be derived by multiplying the unit price by the quantity, rather than being fully dependent upon the primary key. You must remove Total from the table to comply with the third normal form. In fact, since it is derived, it's better not to store it in the database at all but simply compute it on the fly when performing database queries instead. For example, we might have previously used this query to retrieve order numbers and totals: SELECT OrderNumber, TotalFROM WidgetOrders Now use the following query to achieve the same results without violating normalization rules: SELECT OrderNumber, UnitPrice * Quantity AS TotalFROM WidgetOrders Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Email Address Sign up There was an error. Please try again. You're in! Thanks for signing up. There was an error. Please try again. Thank you for signing up. Tell us why! Other Not enough details Hard to understand Submit