Modifying northwind to allow a variable price (or in a new databse)

[chang]

This question is Diferent then the one i got an ansewr to:

Differnce between this and the northwind databsase is here the price varies


I want to create an order, so the person picks name from one table, cutomer
from second table, and then products from third. and then saves it, exactly
like the northwind database, but i have one problem the price varies each
time the order is placed, so how do i store
it that way. My case isthat im a middleman when i get an order i see how
much it will cost me, and
then i decide how much to charge. but it varies each time, and i nead it to
save my cost and the
amount im charging. how would i go about this. thank you

 

[Joe Fallon]

Keep in mind that Nwind is NOT "the perfect way to set up a database".
It is just some sample data to show certain features.

You should have a table of Products (or Items) that have the current price.
This value is the default that is added to an order.
BUT - you can change the default value to whatever you want. (Overcharge or
give a discount.)
The trick is that the price on the order must be stored as part of the order
detail record!

Time sensitive data is often "duplicated" in "violation" of the principles f
normalization.
But it is NOT a violation. It is a necessity for tracking the historical
information for each transaction.

HTH

 

[wzmo]

This question is Diferent then the one i got an ansewr to:

Differnce between this and the northwind databsase is here the price varies


I want to create an order, so the person picks name from one table, cutomer
from second table, and then products from third. and then saves it, exactly
like the northwind database, but i have one problem the price varies each
time the order is placed, so how do i store
it that way. My case isthat im a middleman when i get an order i see how
much it will cost me, and
then i decide how much to charge. but it varies each time, and i nead it to
save my cost and the
amount im charging. how would i go about this. thank you

 

[chang]

Thank you. But I will have a couple of products per order, and i have to
set the price indidvidually per product. from what i understand from your
answer, i can only store a price for the complete oderder. thank you

 

[Jay Vinton]

i have one problem the price varies each

time the order is placed, so how do i store

Add fields for price, qty & orderdate to your orders table.

In general, when a new item is added to an order, the current price is retreived from the price list. When the order is saved, that price is saved with the order. When the order is re-opened, the original (agreed upon) price will display.

Of course, everything depends on your pricing policies.....

Jay

 

[Joe Fallon]

Nope.
You misunderstood.
I said Order Detail table.
In Northwind, that is where the unit price is stored for each item on the
order.
So you can store the correct price for each transaction.

 

[chang]

Thank you. You were right. Just one more question how exactly do i modify
it to do this? I enter into the sub form design of orders and then how/what
do i change the field. Thank you very much