How do 3NF and 1NF compare and contrast in VLDB? What advantage does the 3NF design have for a VLDB?


Chuck Kelley's Answer: 3NF is really good when dealing with large amounts of duplicate data (i.e., not having to store it multiple times), but you will have to do more joins. 1NF will store duplicate data but not have to worry about joins. I think the DBMS you choose will help you determine whether 3NF or 1NF or some other NF is right for you.

Clay Rehm's Answer: There are at least five rules/forms of normalization within a relational database. The goal of normalization is to reduce redundancy and have the most efficient database as possible. However, in the land of data warehousing, too much normalization can cause performance and ease-of-use problems.

Databases within operational systems (your daily bread and butter systems) must be designed for quick update. These databases must have the highest level of normalization that is appropriate so database updates are as fast as possible and the data is not redundant across the database.

The first normal form (1NF) eliminates repeating groups. For example, make a separate table for each set of related attributes and give each table a primary key. This is an absolute necessity in the operational world. However, in data warehousing, data redundancy is not only common but expected since your goal is for your users' ease of use.

The second normal form (2NF) eliminates data redundancy. For example, if an attribute depends on only part of a multi-valued key, move it to a separate table.

The third normal form (3NF) eliminates columns not dependent on the primary key. For example, if attributes do not contribute to a description of the key, move those columns to a separate table. Again, this is a necessity within an operational database.

Depending on your goals and users, strive for the highest level of normalization as possible.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access