Offline database for Internet Outage

[bradolive]

Hi, I have a PHP application that stores sales transactions into database tables, etc - and we have decided to use this as an online service - which makes it great for the client. They aren't computer literate and their computers mysteriously die and disappear. Having off-site data and no software (save a browser) makes them happy.

The problem is that DSL lines often go out for days at a time, so they have asked for a way to at least view their data offline. We don't care about making transactions - they will manually record and enter them later - they agree to that.

The data would be similar to a set of rolodex cards. The cards would have client information and then information about the last few orders - of course it's all in multiple tables, but we can create a select that gets exactly what we need.

The first thought was to have a csv file dumped that they could download, but they would need to figure out how to use it. That seemed to be a show stopper there.

The second thought was to provide a simple html page that was a dump of the main data that they needed with tags so they could navigate around. The problem is that an unscrupulous employee could grab that file and take all of the client data. I'd love to be able to password protect an offline page, but not sure how feasible or functional that would be.

Another thought was to give the client a thumb drive with XAMPP or something similar that had a basic application that could show data from a local MySQL database. The issue is how do I get the data from my server to the client's thumb drive without too much interaction by them. Ideally they would pop in thumb drive, run the startup and launch a web page - click a button and wait for their database to be updated. Yes, I know, we have to rely on them to download/update/sync the data from a working system - so we need ease of use.

I could write code on their local thumb drive that read from the hosted site and wrote to the local, but that means that I need to have an id/password pair for MySQL that can access the data over the internet - I want to keep the localhost database access restriction on my site. I would also need to figure out how to update the code on their thumb drive in case we change our schema or requirements.

Just looking for some ideas or solutions that would be slick. The users of this system are not very savvy and even entering a password seems to be a challenge for them.

[chrishea]

This is an interesting dilemma. If the network isn't reliable, it seems that it would make sense to have a local server or programs running from the PC's that would talk to the remote server and keep a local log / copy of all the data. Running a "cloud" service presupposes a reliable network.

Getting a reliable network would seem to be the right answer but if that isn't possible, then you will have to live with some limitations and potential issues. It seems that maybe you need a dedicated PC that would initiate a connection to the server every 30 minutes or so and extract all of the updates since the last extract. It would need some sort of local program (always running!) that would be able to take some queries and display the data. That might be XAMPP with some local PHP and MYSQL but it could be some Basic or C or something else. Depending on the volume of transactions, you might not even need a sophisticated database in a backup scenario. You can probably deal with the security aspect but if someone steals the backup PC or turns it off or if the program crashes or there are any other problem with it then backup is out the window. You can use one of the remote control programs to manage it if you aren't nearby but once the network goes down, you're out of luck. In that case maybe it also needs a dial-up modem too. Maybe someone will come up with something really slick but it is a difficult set of limitations.

[bradolive]

There are a couple of issues at play here.

1) The customer/client is not capable of installing and maintaining software. Let's just say that the machines are not in an environment that would allow this. Machines do get destroyed or die, but they can easily get a new one. The good news is all they need is a browser. A trip to the local big box to get a PC is easy and reasonable. Most users have to use a touchscreen unit with huge color coded buttons.

2) The network is reliable, but when it does go down, it can be 2-3 days to get it resolved. AT&T business DSL can suffer a cut wire, etc and can cause long down time.

3) Licensing and support. Having them install software that we have to support is not an option. These people can barely type in a password, yet alone install software with a database, etc. If we can control the client's systems it greatly reduces our support effort - and when they stop paying, the functions stop working.

So, basically we need to have a local copy of a subset of the data available for offline work - after all, their data is theirs. We will present the data (like index cards) without giving them program functionality.

Is this a big issue? Yes and no. We hope we don't have down time, but when it happens we need to have the data - that part is critical.

We can dump the file in a readable format (txt file or html file) but they have concerns that it can get into the wrong hands if it's not protected.

Our idea is to allow the manager to download to a thumb drive - preferably in a format that isn't easily read by the average user - and have a program, also on that thumb drive, to decode their data. Very basic protection - not foolproof, but enough to keep their staff fooled. Basically they can't download it, and if they see it, it won't make any sense to them. The system we have replaced uses index cards, so it's not rocket science, but this would be a better level of security.

So after all of this, maybe an option is to dump an html file that is created by our server (in the background, once a day) that manager can download from home, neighboring office, daily (if they think about it) and have it encoded/encrypted - and provide them the decoder/decrypter to read the page. I don't know of any ways to do that at this time, but maybe that's a solution?

[bradolive]

The data doesn't have to be real time - it can be from the previous day. But the thought is they can go to an internet cafe, neighbor office, etc and grab the "data" they need.

Again, we don't need full program capability, just a view of the data that we can create.

Think of it like an ordering system. Sure, it would be nice to add orders, products, etc - but if we are down, temporarily, we could survive if all we can see is the customer record and their last 4 orders.

Live backup PC, etc - not an option. The customer could be next door, or around the world.