Database Normalization, a little confuse

[mine0926]

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:

Customer_Id
Customer_name
Customer_address
Customer_city
Customer_state

orders table's fields:

Order_Id
Customer_Id
Order_Date

order_items table's fields:

Order_Id
item_Id
item_qty

items table's fields:

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

[Tolls]

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.

[mine0926]

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?

|-------------------|
| price_history     |
|-------------------|
| order_id          |
| item_id           |
| price_charged     |
|-------------------|

[Tolls]

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.

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.

just a guess.. will this be the stucture of price_history table?

|-------------------|
| 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.

[mine0926]

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

Now I understand database normalization.

Thanks again,
geje