| |
 |
|
Oracle Tips by Burleson |
Normalizing Data
Designing a database involves a process called
normalization where the data model is broken-down according its
smallest granularity. Invented by Ted Codd and Chris
Date, the term normalization was borrowed from President Richard Nixon
normalizing relations with China in the 1970’s. Codd stated that
if Nixon could normalize relations, then so could the relational
model.
The process of normalizing data breaks the data
down into smaller and smaller tables to reduce redundancy and make
retrieving and managing that data more efficient. In general, if you
find that you have the same data going into multiple rows, you
probably need to break that data out into a separate table. Some of
the benefits of normalizing your data are:
-
Reducing Disk Storage – Since the smaller tables reduce
repeating data, the overall database size is smaller.
-
Ease of Maintenance – If an item changes, then I can
update it in only one place. If my data in not normalized, then I
must update every occurrence of that item.
-
Reduce I/O – To retrieve the data I need, I will be
reading smaller amounts of data from the disk. If my data is not
normalized, I must pull all the data from the disk to find the piece
that I want.
-
Easier Querying – If I store names as “John Garmany” and
I want to know how many friends I have with the first name of “John”,
I have to read the all the names and extract the first name from each
name. If I store the first name and last name as separate items, I
can search through only the first names.
-
Better Security – In a modern relational database, I can
allow a user access to only part of the data. I could restrict access
to sensitive data such as social security numbers while still allowing
access to data regular users need such as names and addresses.
There is a
drawback to normalizing a database. Tables must be joined to recreate
the whole date set, when needed. As mentioned before, normalization
is a process
broken up into steps. At the end of each step, you have a normal
form. After the first step, you have First Normal Form
The second step produces Second Normal Form. There are
six common steps in the normalization process; however, most systems
do not go past Third Normal Form
. Since this
is not a book about normalization, I will introduce the first three
normal forms rather quickly and not dwell on too much detail. You
don’t need more than a simple understanding of normalization to write
SQL.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
