Hello, I learned in school that only in extreme circumstances should derrived values be stored in a database. With that said my problem is this: In my database I have a table that stores invoices (tblInvo). Then there is a table that stores invoice charges.
(tblInvoChg) I have code that analyizes tables and generates charges, and then stores them in tblInvoChg. Those charges are considered derrived values, so they shouldnt be stored at all, right? The reason I decided to store them is becuase it is so complicated
to generate them it would be extremely difficult (and slow) to generate them on the fly. The only two derrived value fields I have in tblInvoChg is Charge description and Charge amount. But now that I think of it all of tblInvoChg is a derrived value because
at any time I can look at the database and figure out WHAT SHOULD HAVE BEEN CHARGED. Can any database GURU out there let me know if I have made an major error, or if it is common practice to allow derrived values in a circumstance such as this. Thanks, Eric
I'm not sure if I qualify as a GURU, but I've done my part with databases.... This is a perfectly acceptable solution, I've done it myself several times. Same as you would denormalize for the sake of performance, it's not 'by the book' but you sometimes have
no choice.
Something like that is not really a derived value. Companies can change prices, and often do. A price that was valid last month may not be valid today. However, a charge for a sold product from last month is still valid today, since the sale actually took place
at a different time. Therefore, the charge should be stored in the tblInvoChg table. Its not duplication of data because there is a real (and significant) difference between a "historical charge" and a "current price".
>> This is very important to me because I may be leving my job soon and I dont want anyone trashing my work after i leave.
Dont worry about that. As a contract developer who has really "been around the block", I can assure you that no matter how well you do your job, there will ALWAYS be people that come behind you and "trash" your work. Thats just the way it is. Your work
will be discredited by those who do not understand it. It will be discredited by those seeking to increase their own footing in the company. It will even be discredited by some if it just doesnt match their particular opinions about how things should be done.
The best thing you can do to limit it is document the crap out of your work and use comments heavily in code.
escherrer
Member
184 Points
42 Posts
derrived values question
Jul 30, 2003 06:52 PM|LINK
Dutch
Member
120 Points
24 Posts
Re: derrived values question
Jul 31, 2003 09:15 AM|LINK
escherrer
Member
184 Points
42 Posts
Re: derrived values question
Jul 31, 2003 12:18 PM|LINK
krome
Member
190 Points
38 Posts
Re: derrived values question
Aug 01, 2003 06:21 PM|LINK
MCSD, MCDBA, MCAD, CIC