Database Reference
In-Depth Information
Denormalization
You know clearly the significance of normalizing the tables in a relational data
model. Normalization eliminates data anomalies, eliminates data redundancies, sup-
ports simpler logic, makes the design application-independent, and encourages data
sharing. But, in practice, we come across cases in which strict normalization some-
times has an adverse effect on data access performance.
Consider the following cases and note how denormalization can improve data
access performance.
PRODUCT-LINE (
LineNo
, ProductLineDesc, ………….)
PRODUCT (
ProductId
, ProductDesc, UnitCost, ………, LineNo)
Foreign Key: LineNo REFERENCES PRODUCT-LINE
Very common data access:
product data along with ProductLineDesc
Normal method of retrieval:
data from both tables by joining them
Improved method:
Denormalize PRODUCT table by adding Product
LineDesc so that all the required data may be obtained from only the
PRODUCT table
STUDENT (
StudentNo
, StudentName, ………………)
CLASS (
ClassId
, ClassDesc, …………………………)
ENROLLMENT (
StudentNo
,
ClassId
, ………………..)
Foreign Key: StudentNo REFERENCES STUDENT
ClassId REFERENCES CLASS
Very common data access:
enrollment with student name and class description
Normal method of retrieval:
data from both the two primary tables through
joins
Improved method:
Denormalize ENROLLMENT table by adding Student-
Name and ClassDesc so that all the required data may be obtained from
only the ENROLLMENT table
Although there are no specific rules on when to denormalize, you must exercise
sufficient caution before attempting to denormalize.
Here are some general
guidelines.
Many-to-many relationships.
Add nonkey attributes whenever joining of the
three tables to get data from the two primary tables is too frequent in your envi-
ronment.
One-to-one relationships.
Even when one table has a lower number of rows, if
matching rows in these tables occur most of the time, consider combining the two
tables into one.
One-to-many relationships.
Add only the most needed attribute from the table
on the “one” side to the table on the “many” side.
Search WWH ::
Custom Search