Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adweigert
Starting Member
22 Posts |
 Posted - 2003-09-27 : 17:32:15
|
| Whats the recommended way for storing history of row revisions where a field is updated that is critical for another calculated value?Example, I have a list of departments and the locale tax for where the department resides.If the tax percentage changes, I cannot change all the previous associations with the department because this will be an inacurate view of what was paid for a product - there are links to appropriation requests and purchase orders, both are affected by the department(s) associated with the item because it changes the TOTAL of the item.Just looking for suggestions...-------------------------------Reading my own post I realized I didn't give a good example of what I mean.Something super simple. I have catalog items. When a critical attribute such as cost changes on the catalog item, I want to basically leave the current item and hide it from being used and then insert the same item details except for the new cost. So that when the item is selected it will use the new cost but any associations with the item with the old cost will remain as they were.-------------------------------Thinking this over I may just have a flaw in my design. Perhaps it would be best to store these critical values in the associated tables. Then critical changes won't affect the item.However I would have to enforce naming constraints in the catalog example where the name or description could not change drastically or you would lose the correct view of the item.I guess that would be easier than storing previous copies of a row.Thoughts? |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-27 : 19:39:53
|
| So if i'm reading this right, you have catalog items, such as for shopping. And when somebody orders an item, it goes into their order. Later, if the price of that item is increased, you're concerned about the effect on the earlier order? I think the typical way of handling this is to actually copy the details of the ordered item into a LineItems table, including the price of the item at the time it was paid, so that you maintain the historical accuracy. Then if a price changes, you just update the current catalog item with the new price and all future orders will reflect that. Yes, this sounds like you are breaking the rules of normalization (debatable), but the goal of historical accuracy demands it.------------------------------------------------------The more you know, the more you know you don't know. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-09-29 : 07:58:43
|
| normally what i do in situation like this is the following.we'll use a product table as an example.a) product table....with permanent attributes...code, name, length, weight, etcb) product_price table with the following info fkcode, startdate, enddate, pricewhen productA is created, a record is put in the product table and a 2nd record is inserted into the product_price table...with the startdate being the first date/time from which the price is effective/valid and the enddate is the forecast last date/time that the price is valid until.initially I set the enddate to an 'infinity' value...ie value until 'forever'...in my case I use 31/12/2099 23:59:59...which for my purposes is "infinite enough".when a price change is required...I update the product_price table to set the enddate to the actual last date/time the price was valid for...and then create a new record in the product_price table with the new startdate being 1 millisecond greater than the actual enddate on the previous record....and with my 'infinity' value in the enddate.I can find the "current price" by searching for records with and enddate = '31/12/2099 23:59:59'....and i can find point-in-time records by performing an appropriate search.this style of solution may solve your problem. |
|
|
|
adweigert
Starting Member
22 Posts |
Posted - 2003-09-29 : 13:24:36
|
| that's really interesting ... it could very well solve my problem ... it makes sense if you really think about it ... thanks ... anyone else care to tune in their thoughts/opinions? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-09-30 : 07:28:42
|
| It's a useful method of retaining a history of changes....The history does need to maintained in such a way that there are no-gaps in the time sequences and that there are no-overlapping times either....i.e. there must always be 1 and only 1 history record for a particular point-in-time. You may need to modify the precision of the start/end times if your regular data can't distinguish changes down to the millisecond...like...the next price change becomes effective at midnight tonight and not half-way through this afternoon when people are already queuing up at the tills....But keeping this in place if fairly simple when you use simple, neat + efficient code. |
|
|
|
|
|
|
|
|
Versioned Rows (History)