Customer Database Table Example

Let us take a look at an example table that contains customer data:

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
  Because this is a basic order table, you can easily see that the table contains the
  1. customer ID, (CustID)
  2. name, (Name)
  3. city, (City)
  4. ordered item ID, (Item_ID) and
  5. description. (Description)
  
  For each item, the quantity and extended total are included. You can quickly see that many elements are duplicated, making the table redundant to read through and requiring additional storage space. The way the data is currently displayed in the table, you would need to add two new columns to include the additional address information and the individual cost per item before this becomes a useful set of information. Normalization, in simple terms, says that there will be one and only one reference to information in a given database, and that tables that use that information will use a pointer (primary key to be introduced later). For example, customer ID (CustID) and item ID above. We will normalize this table in the next lesson, so click on the Next» link located at the top or bottom of this page.c

When a database table is not normalized, it typically means that the data is not organized to minimize redundancy and dependency. Here's what such a table might look like:

1. Redundancy: The same data is repeated multiple times across different rows. For example, if you have a table for customer orders, each order might include the customer's full address, leading to duplication if a customer places multiple orders.
2. Data Anomalies:
   • Insertion Anomaly: You might not be able to add new data without having all the associated data. For instance, you can't add a new customer to the table unless they have an order.
   • Update Anomaly: Updating information can be problematic because the same data appears multiple times. If you change a customer's address, you must update it in every single row where that customer appears.
   • Deletion Anomaly: Deleting a record might inadvertently remove unrelated data. If you delete an order for a customer, you might lose all information about that customer if their details are not stored elsewhere.
3. Complex Queries: Queries might be more complicated due to the lack of structure. For example, extracting unique customer data might involve complex operations to handle duplicates.

Example of an Unnormalized Table:
Let's consider a simple scenario of a bookstore where books are sold:
Unnormalized 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:

• The book title and author are repeated for each sale, which is redundant.
• Customer information is duplicated for each sale, leading to potential update anomalies if John Doe moves or changes his name.
• If you want to list all unique books or all customers without redundancy, you would need to use complex queries or additional processing.

How Normalization Would Help:
Normalization would split this table into several tables, each dealing with separate aspects of the data:

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

This would eliminate redundancy, simplify updates, and make queries more straightforward. However, remember that normalization is not always the best approach for all scenarios, particularly where performance in terms of read operations is critical, or in scenarios where data integrity can be maintained through other means like careful application design.

A database table that is not normalized, often referred to as a non-normalized or unnormalized table, exhibits several distinct characteristics that differentiate it from normalized tables. These characteristics generally lead to inefficiencies and potential issues in data management. Key characteristics of a non-normalized database table include:

1. Redundant Data: There is a significant presence of duplicate data across multiple rows. This redundancy not only consumes more storage space but also increases the risk of data inconsistencies.
2. Data Update Anomalies: Due to redundancy, any update to a data item must be carried out in multiple places. Failure to update all instances of the data can lead to inconsistencies. This is known as an update anomaly.
3. Insertion Anomalies: Inserting new data can be problematic. If certain attributes are dependent on others, the absence of a particular piece of data might prevent the insertion of a new row, leading to incomplete or skewed data representation.
4. Deletion Anomalies: Similarly, deleting a row of data can inadvertently result in the loss of valuable information that is not specifically related to the entity being removed. This is because multiple types of information are often entangled within a single table.
5. Lack of Primary Key: In some cases, non-normalized tables may not have a well-defined primary key, making it difficult to uniquely identify each row of data.
6. Multi-valued Attributes: Tables may contain columns designed to hold multiple values. For example, a column might contain a list of phone numbers instead of a single number, which goes against the principles of atomicity.
7. Attribute Dependency Irregularities: There is often no clear separation of dependencies. Non-primary attributes may depend on other non-primary attributes, leading to a complex and inefficient structure.
8. Complex Queries for Simple Tasks: Retrieving simple data can require complex and inefficient queries, as information is not optimally organized and segmented.
9. Composite Attributes: Columns in unnormalized tables might consist of composite attributes, meaning they contain multiple data points in one field. For example, a "full address" column including street, city, and zip code.
10. Lack of Data Integrity: Without proper normalization, maintaining data integrity becomes challenging, as there are no strict rules to govern the relationships between data types and tables.

In summary, a non-normalized table typically suffers from data redundancy, update, insertion, and deletion anomalies, lacks a clear primary key, and shows dependency irregularities and integrity issues, which make database management more cumbersome and error-prone.