When and how to denormalize a data structure
The accounts payable system in fifth normal form
When to denormalize
- When a column from a joined table is used repeatedly in search criteria, you should consider moving that column to the primary key table if it will eliminate the need for a join.
- If a table is updated infrequently, you should consider denormalizing it to improve efficiency. Because the data remains relatively constant, you don’t have to worry about data redundancy errors once the initial data is entered and verified.
- Include columns with derived values when those values are used frequently in search conditions. If you do that, you need to be sure that the column value is always synchronized with the value of the columns it’s derived from.
Description
- Data structures that are normalized to the fourth normal form and beyond typically require more joins than tables normalized to the third normal form and can there-fore be less efficient.
- SQL statements that work with tables that are normalized to the fourth normal form and beyond are typically more difficult to code and debug.
- Most designers denormalize data structures to some extent, usually to the third normal form.
- Denormalization can result in larger tables, redundant data, and reduced performance.
- Only denormalize when necessary. It is better to adhere to the normal forms unless it is clear that performance will be improved by denormalizing.
Back