normalization

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.
Edgar F. Codd, the inventor of the relational model, introduced the concept of normalization and what we now know as the First Normal Form (1NF) in 1970. Codd went on to define the Second Normal Form (2NF) and Third Normal Form (3NF) in 1971, and Codd and Raymond F. Boyce defined the Boyce-Codd Normal Form (BCNF) in 1974.[3] Informally, a relational database table is often described as "normalized" if it is in the Third Normal Form. Most 3NF tables are free of insertion, update, and deletion anomalies.
A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed for performance reasons.

Consider the following design:

use [test_01];

go

--
--
create table [customer].[contact] (
    [name]         [nvarchar](250),
    [address]      [nvarchar](250),
    [home_phone]   [bigint],
    [work_phone]   [bigint],
    [cell_phone]   [bigint],
    [other_phone]  [bigint],
    [work_email]   [nvarchar](250),
    [home_email]   [nvarchar](250),
    [other1_email] [nvarchar](250),
    [other2_email] [nvarchar](250)
  );

--
--
create table [customer].[contact] (
    [id]          [int] identity(1, 1),
    [prefix]      [nvarchar](250),
    [first_name]  [nvarchar](250),
    [middle_name] [nvarchar](250),
    [last_name]   [nvarchar](250),
    [suffix]      [nvarchar](250),
    constraint [unique_customer_contact] unique (
        [prefix], [first_name], [middle_name], [last_name], [suffix])
  );

create table [customer].[contact_address] (
    [fk_contact] [int],
    [fk_address] [int],
    constraint [unique_customer_contact_address] unique ([fk_contact], [fk_address])
  );

create table [utility].[address] (
    [id]          [int] identity(1, 1),
    [designation] [nvarchar](250),
    [street]      [nvarchar](250),
    [fk_zip]      [int],
    [fk_city]     [int],
    [fk_state]    [int]
    constraint [unique_utility_address_designation_street_city_zip] unique (
        [designation], [street], [fk_zip], [fk_city], [fk_state])
  );

create table [utility].[city] (
    [id]   [int] identity(1, 1),
    [city] [nvarchar](250)
    constraint [unique_utility_city] unique ([city])
  );

create table [utility].[state] (
    [id]    [int] identity(1, 1),
    [state] [nvarchar](250)
    constraint [unique_utility_state] unique ([state])
  );

create table [utility].[zip] (
    [id]      [int] identity(1, 1),
    [zip]     [nvarchar](250),
    [fk_city] [int],
    constraint [unique_utility_zip] unique ([zip])
  );

create table [utility].[phone] (
    [id]     [int] identity(1, 1),
    [type]   [nvarchar](250),
    [number] [bigint],
    constraint [unique_phone] unique ([number])
  );

create table [customer].[contact_email] (
    [fk_contact] [int],
    [fk_email]   [int],
    constraint [unique_customer_contact_email] unique ([fk_contact], [fk_email])
  );

create table [utility].[email] (
    [id]    [int] identity(1, 1),
    [type]  [nvarchar](250),
    [email] [nvarchar](250),
    constraint [unique_utility_type_email] unique ([type], [email])
  ); 

There are valid reasons to de-normalize a relation. Not understanding the relational model is not one of them.

Normal Forms
1NF Table faithfully represents a relation, primarily meaning it has at least one candidate key.
2NF - No non-prime attribute in the table is functionally dependent on a proper subset of any candidate key.
3NF - Every non-prime attribute is non-transitively dependent on every candidate key in the table. The attributes that do not contribute to the description of the primary key are removed from the table. In other words, no transitive dependency is allowed.
4NF - Every non-trivial multivalued dependency in the table is a dependency on a superkey.
5NF - Every non-trivial join dependency in the table is implied by the superkeys of the table.
6NF - Table features no non-trivial join dependencies at all (with reference to generalized join operator).

---

copyright Katherine Elizabeth Lightsey 1959-2013 (aka; my life)

"Every normal person, in fact, is only normal on the average. His ego approximates to that of the psychotic in some part or other and to a greater or lesser extent." - Sigmund Freud