Need to adjust pricing without affecting old pricing

Our database creates proposals, work orders, and purchase orders. Each of those systems pulls from the same price list. I need to be able to adjust pricing for products without affecting the proposals, work orders, and purchase orders. Does anybody know how to steer me in this direction?

Email: jason73178@.hotmail.comYou need to decide how price is set in your case. Is there one price for each transaction (every lineitem), one price for every item at any point in time (all items have a single price at a given time), or what you appear to have now, a single price for any one item at any time.

Three different problems, that actually aren't too closely related!

-PatP|||What we have now is every product has a price, and if we change the price for that product it will change the price of that product in our proposals, work orders, and purchase orders.

If the price of a product changes, we don't want the prices in our old proposals, work orders, and purchase orders to change. Meaning often times the price of a product will be changed by the manufacturer, and we need to be able to update that price. Now we are having to create new products with the extension "v2" at the end of the new model numbers just to accomodate price changes.

What we would like to happen is that when a proposal, work order, or purchase order is made we would like for the form to grap the price from the price list and store it in the document. And if the price of that product is changed by the manufacturer, it won't affect documents with the old price.|||Good plan. What you need to do is include a price attribute in every entity that references an item. In other words, for each lineitem in the quote table, there should be a price column, and the same for the other tables that need to reference price.

Another thing you might want to consider is including a price_history table, to show how item prices have changed over time. This can be a lifesaver when you are trying to unravel things 18 months from now!

-PatP|||My tables are:

tblProduct
tblWorkOrder
tblWorkOrderDetails

Where would I include the price attribute. I'm a beginner so I had a little hard time following the price attribute solution. Also how would I setup a price_history table?

Thank You for the help so far.

-Jason|||The product table would have a Price column and for workorders, since the price applies to the line item, you would put the price column in the details table.

Price history could look like

ID Identity
ProductNumber Whatever datatype you use
ChangeDate DateTime
OldPrice Money
NewPrice Money|||You would include the price everywhere it was referenced in a lineitem. My guess would be tblWorkOrderDetails given the tables you've named. Are there similar tables for proposals and purchase orders ?

grahamt has the right idea for the price history. I probably wouldn't include the old price, although I can see some reasons why you might want it.

-PatP|||I'm still missing something, probably pretty basic. How do I get the current price in the new column I've just made in the details table? And how do I store the OldPrice and NewPrice in order to create a table based on Old and New prices?

-Jason|||When you create a row in the details table, you'd probably get the price from the items table. When you change the price in the items table, you know the old and new prices, and those are what you put in the price history table.

-PatP|||This is a case where you do NOT want cascading updates to your prices. The item prices themselves should be stored with your quotes and proposals, and not just item IDs that link to a table of item prices.

If you want, you can't create a second field in your table called QuotedPrice that is never automatically updated.|||I'm trying, but it feels like I need my hand held on this one. After I add another field to the details table, what do I do with that field? Do I enter in the new price everytime some makes a work order, that won't work? Do I somehow link the field to the current price, if that is the way, how do I do that? I need a little more hand holding on this one and I think I can get it.

-Jason|||I assume (hope) your work orders are being entered through a stored procedure, and not having the user's access the database table directly.

Your stored procedure can record the current price in your work orders table as part of its processing. Alternatively, you could have an insert trigger on you workorders table that fills in the current item price.|||Jason asked me to offer a bit more help, so I'm responding in the forum in case someone else wants to comment. I need somebody to "keep me honest" these days, I'm a wee bit sleep deprived!

I don't know much about your environment, so I don't know what programming tools you might have available. I would expect that in whatever tool you are using to record the detail lines, you have the ability to look up a price from the item number. Once you have the price, in most environments you can simply write it along with the rest of the row.

If that isn't an option, you can create a trigger on your detail line table. A trigger is essentially a stored procedure (a bit of code stored inside the SQL Server) that runs when an event fires. The events that MS-SQL 2000 supports are INSERT, UPDATE, and DELETE. You could create a trigger for INSERT (and maybe UPDATE) that would replace any NULL price values with the current item price.

After you have a chance to digest this, let me know if you are "good to go" or if you'd like more pointers (and a clue as to what you'd like would help me a bunch)!

-PatP