Integration: Scenario

This is the first in a short series on application integration.

Imagine a small business, with an online e-commerce Web Store. The store lets customers browse and purchase products. It is built using ASP.NET MVC, and the data lives in a SQL Server database. Over the years, the store has gathered information on thousands of customers and their purchasing history.

The marketing team decides they need a small application to help them run marketing campaigns. Since marketing groups are very creative, they call the application Marketing. The ASP.NET developers who built the original site are busy, so they hire a small Ruby on Rails shop to build the application for them.

While the marketing application will have its own domain model, it will need to source customer information from the e-commerce application. In other words, integration is needed.

How do we allow these two applications to share data?

What are the approaches at your disposal for solving this problem?

So far we've looked at:

  1. Shared Database
  2. Extract/transform/load
  3. Web Services
  4. Messaging
A picture of me

Welcome, my name is Paul Stovell. I live in Brisbane and work full time bootstrapping my own product company around Octopus Deploy, an automated deployment tool for .NET applications.

Prior to Octopus Deploy, I worked for an investment bank in London building WPF applications, and before that I worked for Readify, an Australian .NET consulting firm, where I was lucky enough to work with some of the best in the business. I also worked on a number of open source projects and was an active user group presenter. I've been a Microsoft MVP for WPF since 2006.

21 Apr 2011

JSON and/or OData as Public service

21 Apr 2011

Assuming the .NET team is too busy to build an integration layer that exposes customer data via the Web Store, the Ruby team could access the SQL Server database directly using a library like TinyTDS. If the Marketing app will be running frequent and expensive queries over the shared data then replicate customer articles to a dedicated Marketing database to reduce locking contention. If necessary, add extra indexes or views to the Marketing slave for more efficient querying.

Nick
Nick
21 Apr 2011

Since the new marketing app will have it's own domain and essentially only needs access to the data, a RESTful service seems like a good fit.

Peter
Peter
21 Apr 2011

Great topic!

For the sake of discussion, here are couple other integration options (though not necessarily the best ones):

  • shared database - the Marketing app pulls directly from the store db
  • ETL - customer data is extracted from the store db, transformed and loaded to the marketing db on a periodic basis
21 Apr 2011

A lot is left out, but we can make some assumptions about the app.

Marketing is interested in long term trends, statistics and data mining. They can accept a small delay in the data (hrs or a day), but when they pull their data it needs to be fast.

Sharing/copying the DB as is would not suit as the data needs to be presented in a significantly different view. The data source to the UI/Model should be centred around that. This is what data warehousing is for. As you said, some sort of integration is needed for this.

A few ways to do this:

  • SQL SProcs (or SSIS if it suits)
  • OLAP, Cubes, etc
  • Data transforms in code

All have their advantages and drawbacks, but it all would work.

My 2c

21 Apr 2011

Peter, nice suggestions. I've posted about the pros and cons of shared database here. I'll try to post about the ETL solution tomorrow.

21 Apr 2011

MilanChe/Nick, I like where you're going ;-)