Skip to main content

MySQL AB __ An Introduction to Database Normalization

Popularity Report

Total Popularity Score: 0
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...

Rank

Related Lists

Bookmark History

Saved by 17 people (-9 private), first by anonymouse user on 2006-04-24


Public Comment

on 2006-08-02 by vivpuri

Database Normalization

Public Sticky notes

When users ask for advice about their database applications, one of the first things I try to help them with is the normalization of their table structure. Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability. This improvement is balanced against an increase in complexity and potential performance losses from the joining of the normalized tables at query-time.

Highlighted by cmorrigu

The first normal form (or 1NF) requires that the values in each column of a table are atomic. By atomic we mean that there are no sets of values within a column

Highlighted by atreyu_bbb

One method for bringing a table into first normal form is to separate the entities contained in the table into separate tables

Highlighted by atreyu_bbb

surrogate primary key -- an artificial primary key used when a natural primary key is either unavailable or impractical.

Highlighted by atreyu_bbb

when using a surrogate primary key it is still important to create a UNIQUE key to ensure that duplicate records are not created inadvertently (but some would argue that if you need a UNIQUE key it would be better to stick to a composite primary key).

Highlighted by atreyu_bbb

There are various types of relationships that can exist between two tables:

  • One to (Zero or) One
  • One to (Zero or) Many
  • Many to Many

Highlighted by atreyu_bbb

To represent a many-to-many relationship in a relational database we need a third table to serve as a link between the two.

Highlighted by atreyu_bbb

When we have a one-to-many relationship, we place a foreign key in the table representing the �many�, pointing to the primary key of the table representing the �one�.

Highlighted by atreyu_bbb

Columns in a table that refer to primary keys from another table are known as foreign keys, and serve the purpose of defining data relationships.

Highlighted by atreyu_bbb

the Second Normal Form (or 2NF) deals with relationships between composite key columns and non-key columns.

Highlighted by atreyu_bbb

The second normal form (or 2NF) any non-key columns must depend on the entire primary key. In the case of a composite primary key, this means that a non-key column cannot depend on only part of the composite key.

Highlighted by atreyu_bbb

To bring the Review table into compliance with 2NF, the Author_URL must be moved to the Author table.

Highlighted by atreyu_bbb

Third Normal Form (3NF) requires that all columns depend directly on the primary key. Tables violate the Third Normal Form when one column depends on another column, which in turn depends on the primary key (a transitive dependency).

Highlighted by atreyu_bbb

In the Publisher table the City and State fields are really dependent on the Zip column and not the Publisher_ID. To bring this table into compliance with Third Normal Form, we would need a table based on zip code:

Highlighted by atreyu_bbb

With our tables now separated by entity, we join the tables together in our SELECT queries and other statements to retrieve and manipulate related data

Highlighted by atreyu_bbb