Sync / send data between front end and back end systems for an Ecommerce site

Question

A client recently had a new backend .net stock management system developed that hooks into their EPOS and allows their stores to keep track of orders, products, customers, etc from a single central database that utilizes MS SQL Server 2003. The client hopes to use this as an opportunity to renew/re-develop their site to also utilize this centralized back end database.

I have experience with e-commerce, but this is my first time developing a site that needs to work with the companies back end systems.

I had been hoping to use an off the shelf platform such as Magento to make the development process smoother and allow for easy upgrades and the use of plugins/modules in the future. However, having looked at the schema for their new backend systems it is apparent that there could be several problems. Their database has a lot of design issues, the tables are badly normalized, and their approach to the database design seems dated to me.

If I was to use an off the shelf platform like Magento, the only way I can see it working is to re-create all the products/categories/customers in the front end DB, this may have to be done manually because of the differences in schema between the backend (centralized db) and frontend(magento db). That would be a mamoth task, and would create a new problem of how to deal with the replication of data (products, stock levels, prices, orders, order statuses). The client currently manages all the products and order data on their .net system and wants to continue to do so, so there would need to be a way to update the sites db when they make changes to their back end systems (E.G in the case order has been shipped/canceled/etc). Like wise the centralized database would need to be updated from the front-end when a customer makes an order, updating stock levels, etc. These are just two examples of data that will need to be synchronized, but their are countless other scenarios.

Does anyone have experience working with Magento or other platforms in situations similar to the above? How did you deal with data replication and sending data between the back end centralized db and Front end db?

I am having a hard time wrapping my head around the best setup/architecture for a site like this, any pointers in the right direction would be really appreciated.

In retrospect I can't help but feel the best solution would be to create an in-house platform from scratch that can directly connect to their centralized database instead of using an off the shelf platform and worrying about syncing data between backend/frontend. Any thoughts?

Answer 1

I would not allow the front-end to connect to the mission-critical database directly. This is a security-issue. Your front-end normally is in the "wild" (public internet) and therefore target for hackers all the day. If one comes through your productive database gets hacked and the company is standing still.

In such situations I always would introduce a tight backend-API to use from the front-end. The front-end can only access backend-stuff to this API; no other way! The API needs to be type-safe and as small as possible - put in only what is really needed. Make API functions for your use-cases is good start (eg. AcceptOrder(Guid orderno). No generic API's where one can manage to inject general queries and logic. In the backend consider every incoming API-Parameter as potentially hacked. This means for example escape them before sending it to any db-engine etc.

The question if you you need a explicit frontend db aside the backend-API depends mainly on the performance of API / network between front- and backend and if the frontend shows data from only one backend-source. In a e-commerce solution it is likely you need one because of performance. In this case I would look at the frontend database as kind of a server side data-cache. Some of the data is can be cached easely (eg. lookup values like categories etc.) others are more tricky (like how many items on stock). Maybe some of them can not be cached in a front-end db at all.

As a start of evaluating the best synchronization strategy I would analyse every table in combination with the use-cases. How often are they used? How short-living/how often does it change is the data within them? etc. I would not do a general two way sync between the databases. Dumping parts of the backend-db to the frontend-db is ok but never do a dumb dump from frontend- to backend-db (security).

Hope this helps a little bit or at least gives you input for your thoughts. Some hard decisions are ahead of you ;-)

Update on SOAP: SOAP would not be the worst technique for this but also not the one with the highest performance; at least not out of the box. Designing a SOAP-API needs some extra thoughts as well like authentication-stuff and bundling lot of data into a few SOAP-calls (SOAP calls are "expensive" so don't try to do thousands of them in a second ;-) )

SOAP ia a standardize protocol and therefore can be used in a lot of programming languages on a lot of platforms. If you know your platform and have both end (frontend and backend) and your control you may find a protocol that more native to your technology-stack and therefore faster. You then can even code your own TCP if you like but be careful with this as it will also need a good authentication and security.

I would start with SOAP or a more native one of my programming-stack (eg. .Net: WCF Binary). You can also think about a REST-API (WebAPI in .Net).

Update on pushing data from backend to frontend: I would consider this only for data that changes a lot and the current value is important. For example the backend can report stock-counts to the front-end. But somehow it feels wrong to me as the backend needs do work for front-end even if there is not one single user on the front-end. So you have permanent work-load on the backend. These things really depends on a lot of details of your project and its use-cases, I would say. Its hard to answer them generally.