Normal Forms   «Prev  Next»

Lesson 6 Limitations of 1NF
Objective Describe the limitations of first normal form.

Limitations of First Normal Form

Question: What are the limitations of first normal form during the normalization process?
While first normal form (1NF) is an important step in the normalization process, it does have some limitations. Some of these limitations include:
  1. Data redundancy: When a table is in 1NF, there may still be some redundant data, especially if multiple tables contain the same data. This can make it difficult to update data and can cause inconsistencies in the database.
  2. Difficulty with complex queries: In 1NF, data is split into separate tables, which can make it more difficult to perform complex queries that require data from multiple tables.
  3. Poor performance: Having data split across multiple tables can result in slower database performance, especially when joining tables to retrieve information.
  4. Increased complexity: As more tables are created during the normalization process, the database structure can become more complex and difficult to manage.
  5. Over-normalization: There is a risk of over-normalizing the database, which can result in tables becoming too small and creating excessive joins.

It's important to keep these limitations in mind when normalizing a database and to strike a balance between normalization and practicality. While it's important to follow the normalization rules to ensure data integrity, it's also important to consider the performance and usability of the database.

Translating into First Normal Form

Translating a table into 1NF alleviates much of the data redundancy and wasted space of a table that has not been normalized. First normal form is only a first step to good design. It is still possible for a table in 1NF to be designed inefficiently. Consider the following relation:
Order (CustID, CustName, Address, Phone, OrderDate, OrderNo, ItemNo, Title, Price, Shipped)

The relation has no repeating groups and is, therefore, in 1NF. But the relation's design is still inefficient. First, the relation describes four entities:
  1. Customers (CustID, CustName, Address, Phone),
  2. Orders (OrderDate, OrderNo),
  3. Item (ItemNo, Title, Price), and
  4. order Line Items (OrderNo, ItemNo, Shipped).
In this relation, a customer's personal information will be repeated in every record referring to an order the customer placed. As noted earlier, repetition increases the amount of time it takes to update that information and makes it more likely that an update will be missed, causing inconsistencies in the database.
What are some of the reasons why it is still possible for a table in 1NF to be designed inefficiently. Consider the following relation:

Concatenated Primary Key

Second, the relation's concatenated primary key[1] of OrderNo and ItemNo means that:
  1. You must know at least one item in a customer's order to enter information about that customer.
  2. You can not enter information about an item until that item has been ordered once.
  3. customer’s only order deletes all information about that customer. (Remember, a customer's contact information is stored along with their orders, not separately. If you delete from your database the only order a customer has placed, you also delete that customer's contact information.
  4. Deleting from the database the only order for an item deletes all information about that item.
You can begin to address these problems by putting your relations into second normal form[2].
The next lesson defines second normal form.

[1]concatenated primary key: A primary key made up of more than one field.
[2]second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.