Normalizing Your Database: First Normal Form

翻译 2011年04月01日 09:17:00

First Normal Form (1NF) sets the very basic rules for an organized database:

  • Eliminate duplicative columns from the same table.
  • Create separate tables for each group of related data and identify each row with a unique column (the primary key).

What do these rules mean when contemplating the practical design of a database? It’s actually quite simple.

The first rule dictates that we must not duplicate data within the same row of a table. Within the database community, this concept is referred to as the atomicity of a table. Tables that comply with this rule are said to be atomic. Let’s explore this principle with a classic example – a table within a human resources database that stores the manager-subordinate relationship. For the purposes of our example, we’ll impose the business rule that each manager may have one or more subordinates while each subordinate may have only one manager.

Intuitively, when creating a list or spreadsheet to track this information, we might create a table with the following fields:

  • Manager
  • Subordinate1
  • Subordinate2
  • Subordinate3
  • Subordinate4

However, recall the first rule imposed by 1NF: eliminate duplicative columns from the same table. Clearly, the Subordinate1-Subordinate4 columns are duplicative. Take a moment and ponder the problems raised by this scenario. If a manager only has one subordinate – the Subordinate2-Subordinate4 columns are simply wasted storage space (a precious database commodity). Furthermore, imagine the case where a manager already has 4 subordinates – what happens if she takes on another employee? The whole table structure would require modification.

At this point, a second bright idea usually occurs to database novices: We don’t want to have more than one column and we want to allow for a flexible amount of data storage. Let’s try something like this:

  • Manager
  • Subordinates

where the Subordinates field contains multiple entries in the form "Mary, Bill, Joe"

This solution is closer, but it also falls short of the mark. The subordinates column is still duplicative and non-atomic. What happens when we need to add or remove a subordinate? We need to read and write the entire contents of the table. That’s not a big deal in this situation, but what if one manager had one hundred employees? Also, it complicates the process of selecting data from the database in future queries.

Here’s a table that satisfies the first rule of 1NF:

  • Manager
  • Subordinate

In this case, each subordinate has a single entry, but managers may have multiple entries.

Now, what about the second rule: identify each row with a unique column or set of columns (the primary key)? You might take a look at the table above and suggest the use of the subordinate column as a primary key. In fact, the subordinate column is a good candidate for a primary key due to the fact that our business rules specified that each subordinate may have only one manager. However, the data that we’ve chosen to store in our table makes this a less than ideal solution. What happens if we hire another employee named Jim? How do we store his manager-subordinate relationship in the database?

It’s best to use a truly unique identifier (such as an employee ID) as a primary key. Our final table would look like this:

  • Manager ID
  • Subordinate ID

Now, our table is in first normal form! If you'd like to continue learning about normalization, read the other articles in this series:



关系数据库 范式(NF: Normal Form) 说明

一.范式概述(NF:NormalForm) 数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新...

三范式备忘 (3 Normal Form)

1 第一范式(1NF) 所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。简而言之,第一范式就是无重复的列...
  • ak913
  • ak913
  • 2012年03月27日 16:25
  • 648

数据库 - 范式(Normal Form, NF)

码设K为R中的属性或属性组合。若K U, 则K称为R的侯选码,或候选键(Candidate Key)。 若候选码多于一个,则选定其中的一个做为主码,或主键(Primary Key)。...

霍夫变换的标准形式--The Hough Transform: Normal form

The Hough Transform: Normal form The flaw The Hough transform described in the previou...

关系数据库 范式(NF: Normal Form) 说明

一.范式概述(NF:NormalForm) 数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新(up...

JBPM工作流之出现Could not synchronize database state with session以及You have an error in your SQL syntax;的异

您举报文章:Normalizing Your Database: First Normal Form