| |
 |
|
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.
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 |
