Database Design: Normalized vs Denormalized Tables

When designing the Database Tables for an application there are many factors that come into play. Often one issue that seems to crop up is the use of denormalized vs normalized tables for representing data. Below is a brief overview of what normalized and denormalized tables are. As for which one to use in your own application that is up to you with many factors coming into play including speed, extensibility, and limits.

Normalized DB Schemes are when you rationalize data objects into tables to reduce and avoid redundancy. A sample would be the below schema where you have a person table, phone table and phone type table. With linkage between the tables to associate the data.

PERSON
ID FIRST_NAME LAST_NAME MIDDLE_INITIAL
PERSON_PHONE
PERSON_ID PHONE_ID
PHONE
ID NUMBER TYPE_ID
PHONE_TYPE
ID NAME

Having a normalized database would allow for a person to have any amount of phone numbers of every type. However if this structure was denormalized then this information would be condensed into a single table that would contain all this information. When doing so it would have to be decided how many numbers a person can have and of which type. One possible denormalization would be to only allow 1 number of each type (home, work, cell, fax) giving the below table design.

PERSON
ID F_NAME L_NAME MID_INIT HOME_NUM CELL_NUM FAX_NUM WORK_NUM

As previously mentioned this denormalization of the data limits how much information can be stored, however it does increase performance of the database in certain as you do not have to do joins for basic select statements.

// SQL //

Comments & Questions

Add Your Comment