calculation problem

[piet123]

yes

Piet, Are you still having problems with the current stock qty thing?

kh

KH, yes i still have plenty problems to overcome but Brianwarnock
have been a great help with suggestions that suites MY! needs.
(see /forums/showthread.php?t=75249)

Like i've said to Brian as well, I do understand the consequences of not calculating the 'current/available stock' value correctly, but remember that this is MY db, MY responsibility and only I will use the db for personal use.

One thing that I also only just now thought about is;

If I change the PRICE value in the ProductsTable, then all of the existing records gets re-calculated with the New price. This is NOT GOOD !
Because on 1/1/2005 I want to change update the ProductPrice with a new value that must apply only to records captured from 1/1/05 onwards.

Anyway, as the history of this thread will show, I still have alot of work to do... And I can only hope that that are more guys like Brianwarnock, who at least understands that I'm trying hard to do something that might seem very simple to most of the other guys here.

Any and all help/suggestions will be greatly appreciated.

Have a good day everyone.
Regards,
Piet.

 

[neileg]

If I change the PRICE value in the ProductsTable, then all of the existing records gets re-calculated with the New price. This is NOT GOOD !
Because on 1/1/2005 I want to change update the ProductPrice with a new value that must apply only to records captured from 1/1/05 onwards.

There are a few ways of solving this problem, but here I would recommend that you store the price in your transaction record. I know this seems to contradict what I've said before about not storing calculated values, but in this case it is the most practical.

 

[Rich]

The price per unit isn't a calculated figure, it's not a violation therefore

 

[The_Doc_Man]

There are two "purist" ways to look at this.

1. Store all prices in a separate table, foreign key = part ID, that is time-tagged twice. Once with the first date on which that date is valid, once with the last date on which that date is valid. Make your JOIN of price to part ID include a test of the date from your price history table.

2. As Rich points out, if you store the unit price in the line-item, there is no violation of normalization because the line-item either contains the date or is the child of an entry that contains the date. So you would have the unit price stored in a table whose keys (directly or indirectly) are date and part ID. STRICTLY SPEAKING, the date should be part of the line-item just 'cause there is a normalization rule dealing with this situation - but in practical terms the date is unequivocal 'cause the purchase order date IS stored in the parent record.

The normalization rule in question says something to the effect that you can't/shouldn't store something in a table if it doesn't depend on all of the keys of the table. Technically, unit price on a given date doesn't depend on the sales invoice number, so this is a nit-picking violation.

Now, here is a REAL wrinkle, but it makes sense in this discussion. If you have the ability to charge a discount for any reason (bulk purchase, preferred customer, special promotion, inventory clearance, you name it), then the unit-price really DOES belong in the line-item 'cause the date is no longer the only key that determines the unit price. I.e. your price table would have a bunch of extra codes (and records) in it to cover the various discounts. BUT the long-term easy way out is to just store the applicable price for that one sale of that one line-item in the line-item table. Compute the effective unit price from some other method, then STORE it. Never again do you have to remember what you charged on that day - or why! (You could, of course, include a field that showed a code for discounts... but you technically don't need that if your business model doesn't need it.)

So in other words, it's a good idea to hold the applicable unit price in the line item entry. But as you know, I go into professor mode sometimes. Since you have expressed confusion on this topic in the past, I wanted you to understand more about WHY it was a good idea.

 

[KenHigg]

Piet, Just curious, are you going to store the value or calculate it when you need it?

kh

 

[Rich]

Calculate it, if he wants to maintain data integrity that is