Okay my project in simple words. Multiple shop sites are hosted on different hosting services receiving orders from customers. All the orders are to be directed to the main company website where the employees will receive the orders, complete and update the system. So its a two way traffic of information between shop sites and the company website.

Its basically a network of content writing websites where orders will be received and sent to the main writers lounge being hosted on the company website. Initially the plan was to build 1 centralized database on the company website and remotely connect all the website to it BUT there are many factors causing the recent change in plans such as security, vulnerability, sustainability of each website and speed issues since all the websites will be connecting remotely to the database being hosted on the company website. There are almost 20 tables in the database, give or take. And the client does not want to invest in the dedicated hosting or anything, hence it has to be different websites on different hosting plans.

I would appreciate any of your professional thoughts on this. Diversify your answer by assuming two situations: 1) All the websites are being hosted with different hosting service providers and read:writer ratio is 70:30. 2) All the websites are being hosted with the same hosting service provider but different hosting accounts and not a dedicated VPS thing. This might help in overcoming the delay issues in remote database queries.

Solutions that we could think of : * Host 1 centralized database being hosted on the company website and connect all the sites remotely to it. * Host a personal database of each website on its hosting. With a separate database for the Writers being hosted on the main company website and copy the data daily from multiple databases to the writers database. Although we don't think this method is really worth it because it will anyways delay the process and hinder the smoothness of the system.

closed as too broad by Mark Storey-Smith, Paul White, Jon Seigel, RolandoMySQLDBA, Mat Aug 14 '13 at 9:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I cannot think of any reason for keeping this thread on hold but you guys are far more experienced than me, so I would appreciate if I can receive a little more guidance from anyone as to how to correct my question. Thanks. – Asim Siddiqui Aug 14 '13 at 11:42

Sounds like a great opportunity to use a webservice to me.

on your customers end, whenever an order is entered, either:

  • have the site update the customers local database and post a webservice call to your backend with the order details.
  • have the order inserted only in the customer local database with a sync flag set to unsynced. have a background process poll for such unsynced orders and post them to your web service.

on your end, do the same in reverse;

  • whenever an order is prepared, post to the originating customers' webservice with the new details so that their data can be updated.

now you need not worry about the database or potentially even the dbms used.

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