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