关闭

Normalizing Your Database: First Normal Form

标签: databasemanagertableeachspreadsheetstructure
341人阅读 评论(0) 收藏 举报

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:

------------------

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

浅谈EF框架(二)--DataBaseFirst

EF框架支持三种开发模型:DatabaseFirst、 ModelFirst和CodeFirst。   这三种开发模式区如下图:        每个模式有每个模型的好处,根据自己实际的需求来...
  • u010108195
  • u010108195
  • 2015-08-01 18:39
  • 940

EF学习和使用(一)Database First

上篇博客中介绍了ORM思想,可以说ORM思想在数据交互方面给我们带来了一次变革。他能够自动实现Entity实体的属性与关系型数据库字段的映射,增删改查的sql脚本由ORM来自动生成,使我们编码时不用考...
  • u010028869
  • u010028869
  • 2015-07-28 17:08
  • 8489

Head First Servlet&Jsp 学习笔记(一) 一些基础知识

Head First Servlet&Jsp 学习笔记(一) 一些基础知识什么是Servlet和JSP用Java开发Web应用程序时用到的技术主要有两种,即Servlet和JSP。 Servlet是在...
  • Peng154
  • Peng154
  • 2015-11-06 00:16
  • 1147

谈谈Oracle数据库的关闭

之前已经说过了Oracle数据库的启动,今天再来看看Oracle数据库的关闭。一、数据库关闭的三个阶段Oracle数据库启动过程包括三个步骤: NOMOUNT -> MOUNT -> OPEN,数据库...
  • pan_tian
  • pan_tian
  • 2014-11-16 01:31
  • 7572

使用AIDE做Linux高级入侵检测文件监控

使用AIDE做Linux高级入侵检测文件监控 1、aide介绍 AIDE(Adevanced Intrusion Detection Environment,高级入侵检测环境)是个...
  • citelao
  • citelao
  • 2016-03-21 15:09
  • 292

使用Django搭建一个简单的Python Web工程

配置好python及django之后就可以使用框架搭建一个简单的web project
  • ps_zhanglei
  • ps_zhanglei
  • 2014-11-12 16:19
  • 2360

code first 数据库创建更新

通过下面的例子我遇到了些许问题。 1.生成数据库后找不到数据库到底在哪 2.用mvc4生成数据库后,手动删除数据库后,没有办法从新生成数据库Cannot attach the file as da...
  • qq_25183269
  • qq_25183269
  • 2015-04-15 13:40
  • 582

Face Normals & Vertex Normals(面法线跟顶点法线的区别)

What is a face normal? A face normal is a vector that describesthe direction a polygon is facin...
  • fox64194167
  • fox64194167
  • 2013-03-03 15:47
  • 2963

openfire源码分析---3

openfire源码分析—3接着openfire源码分析2中的XMPPServer的start()函数,如下所示 ... if (!setupMode) ...
  • conansonic
  • conansonic
  • 2015-10-26 20:36
  • 818

数据库三大范式(Normal Form)

设计数据库时,通常需要遵从不同的规范,设计出合理的数据库,减少数据冗余,而这些规范称为数据库范式,一般来说,只需要实现前三大范式即可。 1.字段不可分。 2.有主键,非主键字段依赖主键。3.非主键字...
  • qq_31821675
  • qq_31821675
  • 2017-06-01 16:43
  • 114
    文章分类
    个人资料
    • 访问:9740次
    • 积分:228
    • 等级:
    • 排名:千里之外
    • 原创:7篇
    • 转载:3篇
    • 译文:1篇
    • 评论:1条
    最新评论