Solved

Storing historical updates in a database

Posted on 2014-10-31
9
115 Views
Last Modified: 2016-02-17
I use the Salesforce CRM to store my donor information. I have an object A which contains two fields Present Update and Past Update(so currently I am storing only two updates). I want to be able to store historical updates(basically one update gets added say every 6 months) so over a 2 year period I would have a total of 6 update fields. So essentially a field would have to be added dynamically every six month. What is the best way of storing this kind of data. I don't necessarily have to store this data in SF. I could store it in a database too.

Please let me know if you need anything else from my end,
Thanks,
Aditya
0
Comment
Question by:giveindia
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
Comment Utility
Storing such information column-by-column is usually a bad idea. A better way would be to have a table which contains the object that was updated, the old value, the new value, and a date field on which the update took place.
You don't specify what data object A contains or what you capture when you update (is it the previous value of the object?), so it's difficult to be more specific. But for example, you have the following table:

ID      Value
1       ABC
2       DEF
3       GHI

Now suppose you update the value for ID 2 to EFG. Your table would become:

ID      Value
1       ABC
2       EFG
3       GHI

And your history (audit) table would contain:

ID    Old_Value   New_Value   Datetime
2     DEF               EFG                2014-10-31 00:00.000

This way you can have as many updates as you like as each update just adds a new row.
Is this something you can use?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I strongly prefer to create a history table whose columns *exactly* match those of the original table (except that "identity" property is turned off; history table is kept in sync: if the original table gets modified, the history table gets the same mod).  As modifications are made, the entire old row is copied to the history table.  You don't need to copy the current row as it exists in the main table.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Scott, I assume you'd add some kind of audit columns as well right? Datetime stamp, user ID, etc?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You could.  Personally, I do that in a separate table.  I want to always be able to use the history table anywhere in code I use original table; and be able to do a SELECT * UNION SELECT * from the original table and its history.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:giveindia
Comment Utility
Apologies if I did not explain the problem correctly.  I have a scheduled task that replaces the last progress update with the current one. So whenever there is a progress update a field would have to be created almost  dynamically. Is that advisable? Should I be storing all previous progress updates separated with a special character ?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Can you give some sample data, and an example of the update process? What's your input and what's your desired output? That will give us something more concrete to base our advise on.
Dynamic solutions usually are a last resort option though, as they're complex to build and maintain, and usually suboptimal when executed repeatedly. But we would only know for sure if we know what you're trying to achieve.
0
 

Author Comment

by:giveindia
Comment Utility
Here is an example. I have three fields in salesforce namely
Beneficiary Progress Update
FeedbackSent
LastProgress Update
I have a scheduled task that checks if the Beneficiary Progress Update has a value and the Feedback Sent is Pending. If it is it copies the Beneficiary Progress Update value  into the Last Progress Update value and updates the feedback sent to pending. So currently I am storing only two progress updates. I want to be able to store historical progress updates.
One other solution that came to my mind was when the scheduled task moves the progress updates to the LastProgress Updates store all previous progress update separated with a special character. Will that work ?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
I think you need to move away from the idea of storing your progress update history in the same table as you are currently using. Create a new table, and insert the historical updates into this table in one of the ways described above.
So when your scheduled task finds len(beneficiary progress update) > 0 and FeedbackSent = 'Pending':

Insert last progress update into new table (e.g. ProgressUpdateHistory)
Update LastProgress Update with Beneficiary Progress Update
Update FeedbackSent to Sent

You probably would have to insert a few identifiers into your ProgressUpdateHistory table as well to identify what it belongs to (I'm sure there's still more data in the underlying table you're not mentioning).
0
 
LVL 32

Expert Comment

by:sarabande
Comment Utility
when the scheduled task moves the progress updates to the LastProgress Updates store all previous progress update separated with a special character. Will that work ?
yes, that can work for numerical values or short (one word) strings. you would make the 'LastProgress' a varchar field and append each next history value to the previous string using a separator like space, comma or pipe symbol.

however, you should be aware of the limitations and shortcomings of that "method". a "history string" is a good visualization for a few values, but not for hundreds. even for 10 values you may have problems to show the whole contents of the field. you also can't add some meta information to the value (for example who had made the update and when) and you badly can search for old values. if any of these points might become a requirement, you should go the relational database way (as described by Koen) and have a second table which is linked by key to the original table and has a date or serial number which it could be ordered by. doing so, allows to show both original data and the history data like

key data history
-----------------------
abc  3      22
                 17
xyz  9       8
                 5
                 6
                 3

Open in new window

and easily add some more columns like date or responsible person.

Sara
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now