Question about northwind database design

siamandm
BronzeLounger
Posts: 1323
Joined: 01 May 2016, 09:58

Question about northwind database design

Post by siamandm »

Hello All,
I have looked at north wind database to learn from it, and I found something confusing if someone can explain to me will be greate
Screenshot 2024-05-15 091918.png
in the products table we have StandardUnitCost and UnitPrice and in the PurchaseOrderDetails we have UnitCost , what are the diffenece between these, as my understanding StandardUnitCost is avaerage cost of the unit , and UnitPrice is the selling price of a single unit of a product
but what is the unicost in the PurchaseOrderDetails
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79694
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Question about northwind database design

Post by HansV »

When you create a purchase order, the value of StandardUnitCost in the Products table is copied to the UnitCost field of the new record in PurchaseOrderDetails.
Why? Even if the StandardUnitCost in Products changes later on, the UnitCost value in PurchaseOrderDetails will remain the same - you don't want the cost of past purchases to be changed retroactively.
Best wishes,
Hans

siamandm
BronzeLounger
Posts: 1323
Joined: 01 May 2016, 09:58

Re: Question about northwind database design

Post by siamandm »

thank you very much for the reply,
what i understood from you is Standard unit cost in product table = Unit cost of PurchaseOrderDetails table and using programming technique will be copied automatically am I right ?

- what about Unit price in Product table what is the use of it and why we need it?

User avatar
HansV
Administrator
Posts: 79694
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Question about northwind database design

Post by HansV »

Yes, the StandardUnitCost value is copied to the UnitCost value by VBA code.

UnitCost is how much the company that uses the database pays to purchase an item.
UnitPrice is the amount they sell it for to their customers.

So UitPrice will almost always be higher than UnitCost.
Best wishes,
Hans

siamandm
BronzeLounger
Posts: 1323
Joined: 01 May 2016, 09:58

Re: Question about northwind database design

Post by siamandm »

Ok, so the Unit Price will be selling price which will be filled during making order and the Unit cost will be the purchasing price from the vendor right?

User avatar
HansV
Administrator
Posts: 79694
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Question about northwind database design

Post by HansV »

Yes, indeed.
Best wishes,
Hans

siamandm
BronzeLounger
Posts: 1323
Joined: 01 May 2016, 09:58

Re: Question about northwind database design

Post by siamandm »

Thank you very much for confirming