Customer Database Table Example

Let’s examine an example table containing customer data to understand the issues of an unnormalized structure:

CustID	Name	City	Item_ID	Description	Qty	Total
000001	Smith	Tucson	100101	Green Widgets	1	$50.00
000001	Smith	Tucson	100102	Blue Widgets	2	$100.00
000001	Smith	Tucson	100103	Yellow Widgets	1	$50.00
000002	Jones	L.A.	100101	Green Widgets	2	$100.00
000002	Jones	L.A.	100106	Orange Widgets	1	$50.00

• Basic Order Table
  This table includes:
  1. Customer ID (CustID)
  2. Name (Name)
  3. City (City)
  4. Ordered Item ID (Item_ID)
  5. Description (Description)
  6. Quantity (Qty)
  7. Total (Total)
  
  The table shows significant duplication, such as repeating customer names and cities for each order. This redundancy increases storage needs and complicates data management. For example, adding address details or individual item costs would require new columns, making the table less flexible. Normalization addresses this by ensuring each piece of information is stored only once, using pointers (like primary keys, introduced later) to link related data. In the next lesson, we will normalize this table to eliminate these issues. Click the Next» link to continue.

An unnormalized database table lacks organization to minimize redundancy and dependency, leading to inefficiencies and potential errors. Below is an example of an unnormalized table, followed by its key characteristics and issues:
Example: Unnormalized Bookstore Table

BookID	Title	Author	CustomerID	CustomerName	CustomerAddress	SaleDate
1	1984	Orwell	101	John Doe	123 Main St, Cityville	2023-10-01 14:00
2	Brave New World	Huxley	101	John Doe	123 Main St, Cityville	2023-10-05 10:30
3	1984	Orwell	102	Jane Smith	456 Elm St, Townsville	2023-10-02 15:45
4	To Kill a Mockingbird	Lee	101	John Doe	123 Main St, Cityville	2023-10-06 09:15

Issues in this Table:

• Redundancy: Book titles, authors, and customer information (e.g., John Doe’s name and address) are repeated for each sale, wasting storage and risking inconsistencies.
• Data Anomalies:
  • Insertion Anomaly: You cannot add a customer without an order, as the table requires sale data.
  • Update Anomaly: Changing John Doe’s address requires updating multiple rows, risking errors if some are missed.
  • Deletion Anomaly: Deleting a sale (e.g., BookID 1) could remove all customer data for John Doe if it’s his only purchase.
• Complex Queries: Extracting unique books or customers requires additional processing to handle duplicates.

Characteristics of an Unnormalized Table:

1. Redundant Data: Duplicate data across rows increases storage needs and inconsistency risks.
2. Data Anomalies: Insertion, update, and deletion anomalies complicate data management.
3. Lack of Primary Key: The table may lack a unique identifier for each row, making it hard to distinguish records.
4. Multi-valued Attributes: Columns may hold multiple values (e.g., a list of phone numbers), violating atomicity.
5. Composite Attributes: Columns like CustomerAddress may combine multiple data points (e.g., street, city, zip).
6. Attribute Dependency Issues: Non-primary attributes may depend on other non-primary attributes, creating inefficiencies.
7. Complex Queries: Retrieving simple data requires complex queries due to poor organization.
8. Lack of Data Integrity: Without normalization, maintaining consistent and accurate data is challenging.

How Normalization Helps: Normalization splits the table into separate, related tables to eliminate redundancy and anomalies:

• Books Table: (BookID, Title, Author)
• Customers Table: (CustomerID, CustomerName, CustomerAddress)
• Sales Table: (SaleID, BookID, CustomerID, SaleDate)

This structure simplifies updates, reduces redundancy, and improves query efficiency. However, normalization may not always be optimal for scenarios prioritizing read performance or where data integrity is managed through other means, such as application logic.