Question about database

views:

answers:

+1 Q:

Question about database

Hi I have problem with two parts of database diagram and I need your guidance:) database has a lot of tables that one of them is about Employee ,the other is about Customer and ... ! I have problem with two tables (Product and OrderDetail) my Product table has 3 columns (ProductID,Name,Cost) and the Other table is OrderDetail that has these columns(OrderDetailID,Cost,Quantity) I have not created this database myself I have found it like a sample database in the internet but I have problem about these two Cost which is in OrderDetail and Product tables ,what is the difference between these Cost? they are the same or not? thanks

EDITED: sorry all I had a mistake ,the ID for the ordeDetail table is the composite of primary key of Product table and Order table which is Product ID and OrderID and the OrdeDeatil table is a weak entity. SO OrderDeatil((ProductID,OrderID),Cost,Quantity)

+2 A:

I suppost that the cost in the products table is the current price of the product. On the other hand, the cost in the order details table is the price that was paid for the product for that particular order.

Product prices can go up or down, or discounts might have been given for particular orders. The field in the order details table would store this information.

UPDATE: Further to your updated question, that makes sense. It implies that each order can only list the same product once. The order details table is storing how many items of that product were ordered (quantity) and I would say that cost is the price paid for one item. It could be the total (price * quantity) but that's not a common for such table structures.

Daniel Vassallo 2010-07-26 06:23:38

is this correct that the Cost in the Product table is unitCost and the Cost in the OrderDetail is the UnitCost*Quantity ? because the PK of OrderDetail is ProductID and the OrderDeatil is a weak entity so when you update the Cost in the Product table the Cost in the OrderDeatil will be update automatically.

2010-07-26 06:30:29

@matin1234: Are you sure that the PK of OrderDetail is not a composite key of (OrderID and ProductID)? Because if it were just ProductID, orders would not be able to include products of other orders... The table structure `OrderDetail(ProductID,Cost,Quantity)` doesn't seem very useful, unless storing information on volume discounts. But in that case, the name `OrderDetail` is quite misleading.

Daniel Vassallo 2010-07-26 06:35:24

yes it is but because I had problem with just these two tables I did not say anything ,OK thanks I will edit it.

2010-07-26 06:37:10

aha for your update ! why it is not common?

2010-07-26 06:45:34

@matin1234: It's not common, because in general it does not reflect how suppliers work. Supplies would normally have a unit price, and if you order 5, they will charge you (unit price * 5) and if I order 6 they will charge me (unit price * 6)... In addition, if the supplier always manages prices with a precision of two decimal places, you you wouldn't want to have a `59.99` in your database and a quantity of `2` (implying a unit price of `29.995`). Storing the unit price avoids this issue.

Daniel Vassallo 2010-07-26 06:51:54

aha I get it thanks

2010-07-26 07:24:37

+1 A:

Without knowing exactly what this database is meant to capture, I couldn't say for sure, but as a rough guess I would expect that the Product tables Cost column is the cost for a single unit of that product, and the OrderDetail tables Cost column is the cost for an order of some quantity of 'something' - most likely a certain quantity of a product. Note as an interesting aside that there is nothing linking the OrderDetail table to the Product table - if these two tables are meant to be linked you would normally expect a Foreign Key in the OrderDetail table linking it to the Product table. At the moment, there is no way to match OrderDetails to Products.

Stephen 2010-07-26 06:25:38

sorry I have edited my post !! the primary key of OrderDeatil is the primary key of Product which is Product ID because the OrderDeatil is a weak entity.

2010-07-26 06:33:47

ansaurus

tags:

views:

answers:

Question about database

related questions