Multiple prices for same product

[faca]

Hello!



I have database (car repair) with 4 tables:

Costumer (Info about costumer and car)

Parts (PartID, PartNumber, PartName, Unit, SellingPrice, PurchasePrice, Store, TaxRate)

Service (ServiceID, CostumerID, Kilometers, DateOfService, Notes, Paid (Yes,No), PaymentType)

ServiceInfo (ServiceInfoID, ServiceID, PartID, Quantity)

Problem is that when i enter new price for part all previous services get auto-updated..

Example:
3 march -> price for motor oil is 80$
Service 1
Service 2

12 march -> price for motor oil increases to 89$
Service 3

Now if ill update price to 89$ also price in Service 1 and 2 will be updated to 89$ (it should stay 80$)

How could i fix this?

Thanks

[orange]

See this link

[faca]

Already wanted to add price to ServiceInfo but the person (mechanic) who is using database must than check price on web or at store for every part again and again.. not good

[orange]

??? Here's another link re Selling Price and historic records that my help.

You can not simply refer to the Price of the Product in the Product table. When you update the Price in the Product table (and that is a common requirement in any business), you will change all your historic Invoice records.
You save the AgreedUponPrice on the Sales detail record.

In effect, the Price of the Product in the Product table is the CurrentProductPrice.
When you store the AgreedUponPrice on the Sales detail record, you are making a permanent record of the SellingPrice of that Product to that Customer on that Order. Which does not affect historic sales records.

If you have a special, or a loyalty program you can not rely on the CurrentPrice of the Product. If you have a 15% sale or you offer special Prices to regular customers after X purchases, then it is an AgreedUponPrice ---not necessarily the current productPrice that gets recorded.

[faca]

got it now thank you for your help

[orange]

Glad you have it resolved.
Happy to help.