By Peter Bell

Integrating Legacy and Web Specific Data

Posted At : December 15, 2006 2:48 PM | Posted By : Peter Bell
Related Categories: Imports
I’m working on an interesting project right now where we need to handle a mixture of legacy data from a small accounting system and web specific data that needs to be added to some products using a web based admin. This post looks at a possible approach to the problem. Got any better ideas?! . . .

[More]

Comments (5) | del.icio.us | Linking Blogs
Comments
[Add Comment]
Peter, we do quite a bit of this type of updates where I work and before I give what I would do, are the fields in the Object imported editable on the web-based system?
# Posted By Phil Duba | 12/15/06 4:06 PM
Most of the time, the imported fields are read only, although in some cases the imported fields do need to support being edited via the website with us exporting those changes to the accounting system via a nightly scheduled file export process.
# Posted By Peter Bell | 12/15/06 5:50 PM
Well, in that case our applications that deal with legacy data or data from another system that's a system of record for that piece of data usually have two types of feeds: one insert/update and one delete/disable. The insert/update feed will always have the legacy applications primary id in it and we store that in some field in our destination table(s). The import tool will look for that field first, and if found perform an update, otherwise, it does an insert. As for the delete/disable, usually our applications will just receive that id and our import tool will set the deleted flag for that set of data.
# Posted By Phil | 12/15/06 11:04 PM
Hi Phil,

Yeah. that's where I'd like to go, but my clients don't have the resources in house to generate such feeds! All they know is what is in the accounting package now - not when changes were made. We both know it isn't that hard to write a logging tool that logs changes, but it is more than they are up for and I don't know the API for their package (or want to learn it), so I just have to have a system capable of taking a full upload and discerning any changes automatically. It isn't actually that bad as long as the number of records isn't crazy and most of my clients have thousands rather than millions of records, so performance isn't too much of an issue.

I'm going to support the two feeds, but also add a layer capable of generating them from a complete update file that I'll just drop in an Import table and query against.
# Posted By Peter Bell | 12/16/06 12:24 PM
One other small distinctoin. To cover all use cases there is an argument for supporting save (insert OR update), insert AND update as explicit insert and update feeds can catch errors and allow for special cases that a "save" feed (which lacks the "is your intent to insert or update" info) may not catch. Not usually an issue, but there are special cases. Blogged about it at:

http://www.pbell.com/index.cfm/2006/11/15/Insert-v...

Thanks again for the comments!
# Posted By Peter Bell | 12/16/06 12:27 PM
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.005.