Sec 578 Course Project Risk Management

Relational Database Design/Normalization

From Wikibooks, open books for an open world

< Relational Database Design

Jump to: navigation, search

Database practitioners talk about normalization a lot, but it's often poorly understood. Some people see it as an academic detail that is impractical in the real world. Many practitioners are convinced that normalization is always too costly in terms of performance. In fact there are important practical benefits to normalization. Even though there may be legitimate reasons to eschew normalization in some circumstances, it is always best to make this decision from a standpoint of understanding the costs and benefits than dismissing it out of ignorance.

Normalization provides a set of rules and patterns that can be applied to any database to avoid common logical inconsistencies. Normalizing a database design will typically improve

* Consistency, since errors that could be made with the database are structurally impossible

* Extensibility, since changes to the database structure will only affect parts of the database they are logically dependent on

* Efficiency, since redundant information will not need to be stored

The database community has identified several distinct levels of normalization, each more stringent than the last. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). It's quite common in practice to speak of one database design as being more or less normalized than another, as defined by these levels.

In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen but is briefly discussed in this article. Fifth normal form means that the SQL selecting newbies won't produce misleading results by doing a join with artificial, spurious rows occurring due to a many-to-many dependency, or something equal...