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?