Exposing ERP data on a public web/ecommerce site - from the SQL point of view

• Don Halloran

  SSCertifiable

  Points: 5937

  More actions

  September 15, 2013 at 8:49 pm

  #297919

  My company wants a new internet presence consistent with the possibilities provided by current technology.

  This means a website that provides e-commerce functionality - the ability for customers browse product catalogues, place orders, view order histories, invoices, etc - as well as providing some reporting functionality - a mix of operational and analytical - specifically for larger customers.

  Now, the company I work for is reasonably large, we're not talking about a basic computer parts store here. Annual revenue is about 1.2 billion.

  As such there are some serious architectural decisions to be made for this project.

  From the data point of view, the point of view of SQL Server, perhaps the most fundamental question is this: should the public website communicate directly (in real time) with the ERP system in terms of both presensting information to customers as well as the entry of orders back into the system, or should the public website communicate with its own bespoke database which is synchronized with the ERP via managed processes?

  Right now, looking from a SQL point of view, I fall pretty firmly into the latter camp. Giving the system its own database...

  - you can index appropriately for what could be an entirely different load pattern

  - you can guarantee the security of information that should not be exposed since there is no channel from the outside world to the internal system.

  - It keeps business users from interfering with website functionality and vice versa (locking, transactions, etc)

  - it allows the creation of a data model suited specifically to the website rather than being stuck with the one provided by the ERP

  - etc etc

  In general, it gives you all of the advantages that come from a separation of concerns. As a guy who strongly believes in the value of theory and principles, this approach sits well with me.

  The difficulty, of course, is the pratical need to come up with a robust, timely synchronization process.

  Does anyone have an opinion on this matter, and could you provide the reasons you have for holding that position? What about technologies used in the bespoke database solution... replication? ETL? ESB?