Database Table Design: 0th Normal Form (0NF)

 

Database Table 0th Normal Form (Spreadsheet Design)

Definition Examples and Recommendations

Zero Normal Form Definition

0th normal form is the classic spreadsheet layout for a database table. Characteristic of a spreadsheet layout are:

  • Repeating groups of fields

  • Positional dependence of data

  • Non-Atomic data

Example

The following table is in spreadsheet design:

Project_Name
Project_Location
Milestone_1
Milestone_2
Employee_Names     (example data in a single cell would be “Jane Doe,
John Doe, Billy Bob”
Emp_Hire_Date
Sales_2001
Sales_2002

Problems With Data Stored in Spreadsheet Layout

  • Data duplication

  • Single fields contain multiple data values (Employee_Names), therefore they are non-atomic

  • Queries would require constant update as because of positional dependence of data (sales_2001, etc.)

  • Tables/forms/queries/reports require redesign when a additional milestones or sales years are needed

How to Fix The Problems

All repeating groups should be split out into a separate subordinate table with the column ‘data’ defined in a table field.  Example for the Project_Sales table would be a table with the following fields:

Project_Name
Sales
Year

To solve the problem with multiple employees listed in one field create a subordinate table like:

Project_Name
Employee_Name

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016