Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15

    Some confusion about normal forms

    Hi,

    I want to write an article about database normalisation for a dutch forum, so I've been reading different articles about it, but saw some contradictions between them.

    Let's say whe have the table order_details with columns:
    order_id, product_id, amount, unit_price, total_price

    Some articles state that the column total_price should be removed to reach 1NF, but in imo is that part of 3NF. Am I correct?

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    In reading a number of articles on 1NF, they all pretty much state the following:

    1. Every column in the table must be unique
    2. Separate tables must be created for each set of related data
    3. Each table must be identified with a unique column or concatenated columns called the primary key
    4. No rows may be duplicated
    5. no columns may be duplicated
    6. no row/column intersections contain a null value
    7. no row/column intersections contain multivalued fields


    I am not seeing how total_price is violating any of these rules.

    However, when it comes to 3NF, total_price can be derived by multiplying the unit_price by the amount, therefore it's not fully dependent upon the primary key. Strictly speaking, you must remove it from the table to comply with the 3NF. This means that every single query asking about the the total price will need to do something like:

    Code:
    SELECT order_id, product_id, amount, unit_price, 
           amount * unit_price as total_price
      FROM Orders
     WHERE order_id = 5
    This is where experience and wisdom of the developer comes into play to determine if this is the way to go or if breaking 3NF has other benefits in the bigger picture.

    For example, I have a table with:

    Taxable Value
    Tax Amount

    The tax amount is calculated by multiplying the value by a rate that comes from another table. I found that I was always summing tax amounts and comparing taxes charged against taxes paid. It was much, much easier to have the tax amount always computed then to have to do it on the fly.

    The other option to maintain 3NF is to declare the total_price as a computed column in the DDL.

    Code:
    Create Table Orders
    (order_id int,
     product_id int,
     amount decimal(15,2),
     unit_price decimal(15,2),
    (amount * unit_price) as total_price)
    Last edited by LinksUp; 03-07-13 at 02:01.

  3. #3
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    I am not seeing how total_price is violating any of these rules.
    Nor do I, so thank you for the confirmation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •