Results 1 to 5 of 5
  1. #1
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default Database Normalization, a little confuse

    Good Day :), I a trying to learn database normalization, I have read this page, this page is very easy to understand and I was able
    to understand what they are tying to point. But what confuse me is the way they seperate the item_price in the order_item table.

    At the page I have read, in the ending of tables where these are analyzed and normalized, they
    put item_price to the item table and did not put price on order_item table.

    Here are the tables thay use.
    customer table's fields:
    Java Code:
    Customer_Id
    Customer_name
    Customer_address
    Customer_city
    Customer_state
    orders table's fields:
    Java Code:
    Order_Id
    Customer_Id
    Order_Date
    order_items table's fields:
    Java Code:
    Order_Id
    item_Id
    item_qty
    items table's fields:
    Java Code:
    item_Id
    item_description
    item_price
    I believe item_price should not be removed at order_items since item_price will depend on the order_id, and both table (order_item and items) item_price field.
    I thought this because the item_price at items table may increase/decrease but should not affect the price that the customer orders which will be saved at order_items table.
    BUT, if both table(items and order_items) item_price will it violate 1NF's rule? Or is it OK?

    Any explanation will be greatly appreciated.

    Thanks,
    geje
    Last edited by mine0926; 08-25-2010 at 08:14 AM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default

    In real life?
    I think you're correct.
    Unless items has a history (ie the prices are held in a price_history table) then, presuming prices do not remain constant, the order should hold the price charged. So the order_item has a price_charged, and the items has a current_price.

    With a price_history the price_charged would be a key to the correct entry on the price_history table. You could probably figure out the structure of that. It would be quite similar to how most currency exchange rate stuff works.

  3. #3
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    Thanks for your reply. At least I was able to understand the concept of database normalization..
    Unless items has a history (ie the prices are held in a price_history table) then, presuming prices do not remain constant, the order should hold the price charged. So the order_item has a price_charged, and the items has a current_price.
    So you mean I am right that both table should have a price? What about 1NF's rule, if both order_item and items table has a price field does it violate the 1NF? Or it does not because they are use in different su=ituation?


    With a price_history the price_charged would be a key to the correct entry on the price_history table. You could probably figure out the structure of that. It would be quite similar to how most currency exchange rate stuff works.
    Thaks for this suggestion, I might never thought of this. if I will put price_history table then I should not put item_price field at order_item table?

    just a guess.. will this be the stucture of price_history table?
    Java Code:
    |-------------------|
    | price_history     |
    |-------------------|
    | order_id          |
    | item_id           |
    | price_charged     |
    |-------------------|

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default

    Quote Originally Posted by mine0926 View Post
    Thanks for your reply. At least I was able to understand the concept of database normalization..

    So you mean I am right that both table should have a price? What about 1NF's rule, if both order_item and items table has a price field does it violate the 1NF? Or it does not because they are use in different su=ituation?
    Ah, but they're not the same thing (as you have seen), and this is quite important. One is the price charged during that transaction, and the other is the current price. This is where the authors have gone wrong.

    Quote Originally Posted by mine0926 View Post
    Thaks for this suggestion, I might never thought of this. if I will put price_history table then I should not put item_price field at order_item table?
    Yep, you'd have a foreign key to an entry in the price_history table instead, which would be the current price.


    Quote Originally Posted by mine0926 View Post
    just a guess.. will this be the stucture of price_history table?
    Java Code:
    |-------------------|
    | price_history     |
    |-------------------|
    | order_id          |
    | item_id           |
    | price_charged     |
    |-------------------|
    No need for order_id. The price history is solely related to the item, not any particular order.

    The order will have a foreign key to the price_history, which should have its own id for purposes of a primary key. This id could either be unique (the easiest and probably most used option), or form the primary key in conjunction with the item_id. I'd go for the former since you can the simply rely in whatever auto generated keys the db you're using has.

  5. #5
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    Thanks for the time you give here and for replying/explaining everything to me.

    Now I understand database normalization.

    Thanks again,
    geje

Similar Threads

  1. Use the database from other pc
    By AbdulAziz Bader in forum JDBC
    Replies: 3
    Last Post: 06-07-2010, 01:59 PM
  2. Replies: 0
    Last Post: 03-28-2010, 05:40 AM
  3. Database Help
    By simtology in forum New To Java
    Replies: 3
    Last Post: 08-18-2009, 03:03 PM
  4. Database Help
    By simtology in forum New To Java
    Replies: 1
    Last Post: 08-17-2009, 06:14 PM
  5. Replies: 2
    Last Post: 02-11-2009, 04:43 AM

Posting Permissions

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