Synchronsing office and website databases?

[bel]

What are some ways to achieve this:

There is an MS Access database in the office and a database with the website (yet to be developed), which is share-hosted remotely on a third-party web host.

The databases contain club membership and fee payment records.

There are other tables, forms and reports in the office Access database that are being used, but are not relevant to the website.

I need to keep the tables that will exist both database synchronised, both ways.

I imagine its least complicated to manually trigger the sync, say by the office database operator who clicks on a button in Access periodically or when required.


So how can this be done and what website database would make it easiest(Access, MS-SQL or MySQL)?
I usually prefer to use MySQL for my website databases as I like using phpMySQL with it, and MS-SQL is usually costs more (higher hosting fees).

Does it matter that the website is share-hosted on a third-party server, does this restrict what can be done?

The web app language I use is Coldfusion.

Thanks,
Bel.

[tripy]

I don't think you can have it done easily both way...
With sql server (even express edition) or mysql, you can setup a replication, that would make the "slave" database identical to the "master" database, but changes on the slave would result in an out of sync slave. The changes have to go through the master node.

Does it need to be real time synchronization ?
If not, another solution would be to denormalize totally the dbschema, using only 1 row to store everything.
The syncing procedure would be, at this point, to extract insert statements from both db, and mix them.
If no ID's are to be gathered, and no foreign key enforced, this could work, I think.
But it's heavily dependent of the schema and the quantity of datas.
Obviously, dropping and reconstructing the table if it contains 10 millions row would not be a sustainable solution in the long run.

Does it matter that the website is share-hosted on a third-party server, does this restrict what can be done?

Not inherently, but this means that clustering is not possible (I suppose here that the hoster will refuse to list your server as a slave of his) and that if you run into performances problems during the sync (depending of the time it takes to sync) you might end with your account suspended.

[mickthetree]

Hi Bel

I have just built a database for an auction company that have an old ms-dos program in house and they want the two to have some data sync'd.

They can export from their inhouse database in a csv or txt format and I have built a php script that will open the csv file and pull it into the web database.

This is a one way only system at present from the in house system to the web system, but might be something to look into.

There are some off the shelf csv importing scripts out there too if it helps.

[Dr John]

I do exactly this regularly for a club

The office application has an option to extract the data to a csv file (it could also be to a tab delimited file, as this gets round problems with odd characters in the actual data itself). The extract is from a predetermined date to the current date - we start with from the 1st January for the current year, then I change it every few months to, say, 1st March, 1st July, and so on - the office staff are not very computer literate. The two extracts are actually a view - the result of querying several tables - the denormalised approach suggested by tripy above. This simplifies things for our members who just have a few choices to make to get at their own data, via some queries I wrote for the web site and some radio buttons.

Then, using SQL Manager 2007 Lite (which is free), I fire off a set of sql commands like those below, to remove the older equivalent data, then replace it with the same data + the new data. (If we could trust the office staff to do things properly, we'd just add a week's worth or a month's worth at a time, but they find it easier to just click the extract button rather than alter things and get it wrong, as if they give me the wrong start date, things can go very wrong indeed.)

delete from mycounts where fdate >= "2009-01-01";

load data local infile "c:/Users/Dr John/Documents/logpile/AccountDataExport_2009-01-03.txt" into table mycounts;

delete from myflights where fdate >= "2009-01-01";

load data local infile "c:/Users/Dr John/Documents/logpile/FlightDataExport_2009-01-03.txt" into table myflights;

This is very quick to do. I keep a copy of the queries in a text file, and just edit the dates then copy and paste it into SQL Manager.

I'm sure you could do the same with Access, exporting a query to a file.

However, this is a one-way synchronisation. The online copy either matches the office or is a week or so behind it. no new data is added to the online except by the above queries.

Oops, nearly forgot. As this involves displaying payments they have made, there is a secure log in and they can of course only get at their own data. You'd need to have a secure log-in as well.