Its fundamental to the database relational model and avoid redundancy by
ensuring that each 'fact' is stored once and once only. Take this table:
ContactID FirstName LastName Address City
Country
------------------------------------------------------------------------------
1 Tony Blair 10 Downing Street
London UK
2 Gordon Brown 11 Downing Street
London UK
It tells us twice that London is in the UK. This is not merely inefficient,
more importantly it leaves the door open to update anomalies as there is
nothing to stop a row being entered, for Buckigham Palace say, where London
is put in the USA (there are in fact 3 Londons in the USA and 1 in Canada to
my knowledge, but they are different Londons to the capital of the UK).
By decomposing this table into 3 tables this redundancy is avoided.
ContactID FirstName LastName Address CityID
------------------------------------------------------------------------------
1 Tony Blair 10 Downing Street 1
2 Gordon Brown 11 Downing Street 1
CityID City CountryID
----------------------------------
1 London 1
2 Bristol 1
3 New York 2
4 Dallas 2
5 Paris 3
CountryID Country
----------------------
1 UK
2 USA
3 France
The above is very much over-simplified of course as it omits other
geographical inits like States, but I hope it illustrates the basic principle.
This process of eliminating redundancy by decomposition of a table is called
normalization. There are formal rules which determine if a table is in a
Normal Form, which range from First(1NF) to Fifth (5NF). There are higher
normal forms in fact but these are a little more bizarre. For practical
purposes normalizing to Third Normal form(3NF) is generally OK. You'll find
a guide to normalization, with good diagrammatic illustrations, at the
following link:
http://www.datamodel.org/NormalizationRules.html
Ken Sheridan
Stafford, England
