Normalization

The tables in an RDBMS database should ideally be designed to be normalized.

• Improves update efficiency,.
• Removes many causes of anomalous data dependencies
• Allows better checks for consistency.
• Is (usually) better for query handling.
• But: computational penalties in some SQL operations.

Essentially, normalization means that no data item should occur more than once in the tables; duplication should be avoided.

Foreign keys are vital for creating normalized database designs. Data that is present in several copies in one table should be split out into a separate table, and a foreign key should be created from the original table to the new one.

For example, the tables in the example contain a column 'species' that contains identical strings. This should be normalized:

• The strings should be moved into a separate table 'Species' having columns 'number' and 'name'.
• The tables 'Gene' and 'Protein' should use foreign keys to refer to the relevant row in 'Species'

There are several different levels of normalization (normal forms). See for example this Berkeley lecture on database desing for more information.