Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

My company is an online retailer, and we use an homebrew ecommerce platform. In the last year or so, we've partnered with a accounting software vendor, as our homebrew solution was unable to produce reports as needed, or otherwise satisfy the requests of banks and investors.

We're off to a rocky start with the software, but I'm wondering if it's our approach. Currently, we store a complete set of data in the database connected to our ecommerce platform. Nightly, we send the necessary API calls to sync our entities in the accounting software (orders, POs, invoices, etc), and each morning, I've created scripts that validate that the data is correct between the two systems. This collective system has been a nightmare, as any minor chages to the ecommerce platform cascade in violent and horrible ways to system B. It's resulting in a TON of work on my part to make corrections on a daily basis.

Is there a correct way to approach this problem? Or does the problem really lie upstream, in our decision to duplicate the data between the two systems. Has anybody successfully done something similar?

It has been a non-stop fire drill for 3 months now, and any advice would be greatly appreciated.

share|improve this question
    
It sounds to me like schema changes (and their corresponding sync/reconciliation scripts) aren't being tested properly in both systems before being deployed to production, is that the case? –  FrustratedWithFormsDesigner Jun 20 '13 at 18:35
1  
Have you outgrown the home brew e-commerce platform? Maybe it's time to move to a commercial product/service that is known to integrate well with your chosen accounting software. –  Dan Pichelman Jun 20 '13 at 18:59
    
What is the nature of the API you have to call? Are you calling the Accounting co's own API, or are you trying to mirror exactly your data so they can run reports against a copy of your database? –  GrandmasterB Jun 20 '13 at 19:14
    
FrustratedWithFormsDesigner - that's a safe assumption. We move pretty quickly around here, sometimes at the cost of what's downstream. –  a453803 Jun 26 '13 at 22:51
    
Dan Pichelman - I think we've got more growth ahead within our current platform, we aren't specifically feeling the pinch of it's inability to scale and change. –  a453803 Jun 26 '13 at 22:53

3 Answers 3

The overall description of the architecture doesn't suggest 'bad'. I don't think it that something like that is even uncommon to have separate schemas/databases for each business function (accounting being separate from sales/marketing) as it respects certain data ownership principals and responsibilities.

The problem it sounds like you are having is that the ecomm site is too disconnected from the mapping-sync that coordinates with the accnt system.

Is the mapping-sync developed using shared/reused code, or is it completely divergent, perhaps produced in a different language and running in a different operating context/environment (eg main ecomm in Php, while mapping-sync is in Perl?

Likely the mapping-sync isn't making decisions about the data, but merely transforming and transmitting it. It is possible to keep this process better aligned with the ecomm side by parsing the relevant ecomm bits that describe the data structures and constraints, and generating the pluggable bits for the mapping-sync side, such that any changes to the ecomm side will almost automatically be reflected in the mapping-sync.

share|improve this answer
    
So our e-comm platform and the software being used to sync the systems is the same codebase, etc. But you're correct in saying "the mapping-sync isn't making decisions about the data". We build a transaction log in DB triggers, and the software basically just takes those transactions, converts them into accounting software friendly format, and fires it off to the API. There are a few decisions being made such as "is this order line cancelled, if so, tell accounting soft that it's cancelled" but even simple tasks such as this are quite complicated. –  a453803 Jun 26 '13 at 23:06
    
A straight alternative to generative programming your sync interface is doing some meta inspection and programming. This might make the startup time longer for that sync process, but having a more adaptive reader interface might make maintenance a bit more hands off. Can you dynamically pull a schema from the A side database and combine it with some general conflict resolution rules to perform meaningful and correct updates? Even if you did this for the less complex transaction types, it may free you up enough to eventually work out a general solution for the more complex bits. –  JustinC Jun 27 '13 at 2:30
    
The approach sounds interesting. I do have full access to both DBs, and they're both MSSQL, but can't say that I fully understand the suggestion. It's possible that 600 characters won't cover it. Is there any chance you could share a link that might help me wrap my head around it? Thanks so much! –  a453803 Jun 28 '13 at 3:17
    
A more meaningful, concrete example would depend on the technology stack. Modern MsSql will emit an XML schema through built in TSQL commands. Take a look at @George's answer and comments that follow. Note that is just for one particular table, and relationships (or other logical elements) do not come with it, just the physical table structure. Another possibility, which I haven't personally used, but seems rather popular is the Sync Framework –  JustinC Jun 28 '13 at 6:29
    
Ah, I see. So, I think the problem with DB to DB syncing is that I would have to write all of the business rules that the accnt soft is currently taking care of. For example, when I submit an order through the API, the accounting package takes care of allocating inventory, if available. So, I would need to not only write a direct mapping for my two order entities, but then also scripts to perform any additional logic, which I may or may not already know of. I'm thinking a better version of my current solution that better understands the entities like you suggested initially might be in order! –  a453803 Jun 28 '13 at 13:54

Why duplicate data this day and age when you can just move to a Service Oriented Architecture? Have the accounting system put a web service on their data, then have your system call it when it needs certain pieces of data and vice-versa. Don't store the entities in your database at all. If you need to reference a record from the accounting system, store a key or a token. Then, let the service/controller layer take care of reassembling the data by making web service calls to fill in the missing pieces.

share|improve this answer
    
Good question Jonathan. We currently have ~ 10 years of data in a DB that feeds our e-commerce solution. Shifting the primary home of that data into the accounting system is not really an option, as our entire infrastructure relies on it's current location. I understand that breaking that setup and going to an SOA would limit this type of dependency, but it's a massive undertaking from where we stand, and a bit more dependent on the third party than we would enjoy being. –  a453803 Jun 26 '13 at 23:02

You need to change your development and quality assurance procedures.

You have a dependency of accounting on e-commerce. Which is fine. But that means that you are no longer free to make changes to the e-commerce system without considering the accounting system.

Any changes made to the e-commerce system now need to be evaluated and tested for their effect on the sync scripts and the accounting system BEFORE they go into production.

share|improve this answer
    
This is a correct statement. –  a453803 Jun 26 '13 at 23:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.