6030 课下笔记：database normalization

01. concept

<br />                    

02. normalization

This is an example of a table in a database. The table is called Customers, and represents the entity “customer.” The attributes of the customer entity are Name, DOB, Address and Phone, which are the headers for the attributes of this table.
Let’s compare this table to another table that represents a customer entity in a different way:

This table is called Customers_Redesigned, and this entity has the following attributes: ID, LastName, FirstName, DOB, StreetAddress, City, State, ZipCode and Phone.

Considering the first Customers table, how can we uniquely identify a single customer? In the second table, Customers_Redesigned, each customer has a unique attribute, ID. Using this attribute, the database user can easily distinguish between two different customers with the same name.

The Customers_Redesigned table is also preferable due to the granularity (粒度）**of the attributes**. For example, we may be interested in the first name of some customers, but not interested in their last names, these are easily distinguished when FirstName and LastName are stored as separate attributes.

<br />                    

The difference between these two tables is in the data model. In the first table, each customer is modeled by four attributes, while in the second table, each customer is modeled by the same information plus a unique ID, using nine attributes in total. When it comes to the design of a data model, there is not one right answer. The best data model is determined based on the context of the problem and how the data is being used. There are some best practices for data modeling, and applying these best practices can **reduce the storage required for the database and can improve performance for certain database operations**. Edgar Codd, a computer scientist who worked for IBM, defined these best practices, broadly known as normalization. As we continue in this unit, we will learn a more about concepts in database normalization.

<br />