Web ERP using PHP,Mysql - intranet/internet configuration


I’m currently developing a web ERP (Enterprise Resource Planning System) for an organization. I’m using Cakephp, mysql, and javascript to develop the application.

  • The organization is made up of 6 offices in 6 different locations.
  • The application (being web) should also be available on the internet and accessed from any browser.
  • The internet connection found in the 6 offices is below 2Mbps.

The employees from all offices should be able to enter a lot of daily data into the application. The problem is the following:

Due to a slow internet connection, i can neither place the web server in one of the offices (so i have to place it elsewhere), nor make them enter the daily data through the internet every time.

I was thinking if there was a way i could make each office work on the application as individual “intranets” to speed up the process (i.e. place one webserver in each office along with the mysql db) , but they would all be connected to each other and to the internet (with an external internet web server), because all the data should be displayed in real time to everyone.

I have seen something similar done in C#/microsoft sql server business applications, in which they use VPN to perform “almost” real-time database replication.
However i do not know how this can be done using php and mysql. I read about mysql replication on the mysql website, but it seems it’s not done for such purposes but rather for backup.
I have looked in every possible forum and surprisingly could not find any clear solution.

Aren’t there php-mysql applications that work on intranets and yet are connected to an external webserver ?

Any help would be greatly appreciated.

I would guess it would depend on how large the pages are that are served - but one option, could be to locate the database on the Internet and place a web server in each of the locales.

This would avoid the need for replication and the external traffic on each site would be limited to obtaining the data from the database.

This is a very good idea. In fact it has crossed my mind; but as you said, it depends on the served pages’ size, which in this case, is minimal.

I am relying a lot on JSON transfer from web server to browser in order to limit transfer to (approximately) database data, and i’m putting focus on javascript (jQuery in this case) to draw the HTML based on the data. (This design decision was affected by the slow internet connection).

As a result, placing the database far from php would probably slow down the application as querying from php to mysql is greater than the page size from php to browser.

If the page size in minimal, why not place the whole application off site?

Minimal but not zero.
Using the method mentioned above, i was able speed it up as much as possible. Unfortunately, the data transfer is very big and frequent. If I don’t manage to make it “look” like a desktop application (with vpn database replication) , I might need to re-develop the application on a different platform (like C# desktop app. with sql Server replication).

As i’m not very fond of desktop apps , i’m looking for possible solutions to make a web app have the speed of a desktop app with the web advantages.

Sorry, you’re losing me a little. Whether you like it or not, this data is going to need to over the WAN no matter which solution you implement. As you’ve already decided you need real time, synchronised, data - then a single data store would be the only way to go.

As ‘there can be only one’, placing a store on each site is out of the question by design. Just create a standard, web based application and you’ll be good to go. If you need to be able provide cross platform functionality, create a web service to perform your standard CRUD operations et al and build the, externally hosted, web application on top of that.

I guess you’re right. I am not making much sense, i’m asking for something impossible :slight_smile:
Ok, so to go back to reality,
I still have 2 questions:

  1. Can you please clarify this option or provide me with an explanatory link for :
    create a web service to perform your standard CRUD operations et al and build the, externally hosted, web application on top of that

  2. If i decide to sacrifice real time and go to more or less (pseudo-real time) how close would it be to actual real time and what would be a good design then ?