Synchronising Databases..

Kali

bit of a potential problem..

a company has a large internal database in an office.
they want it available on the web (for remote access/editing) also.

they only have an isdn line... so hosting one local db won't work, and 90% of the access to the database(s) would be within the office so no point in having one db remotely hosted.
which leads me to having one db hosted on the net for external access etc. and one for their internal work..

which leads me to the problem.. synchronising the databases has me a bit confused.. i've used myqls replication commands to mirror databases before but i dont think its whats needed here (more a 2-way type thing..)

anyone any suggestions? if you need more info let me know. (for arguements sake lets say they're both mysql databases accessed via apache/php)

cheers for any help offered

dahamsta

Tricky. I've never actually implemented this, but I have thought about doing it with a site management yokey-bob I don't have time to work on. The way I was going to approach it was to store all queries (in a table) as changes were made in the UI. Then when the user went online, you would push the (encrypted) queries to a handler on the server, which would run them all. And vice versa.

There's a problem with this in a multiple admin situation though, if a local user and a remote user edited the same record in between sync operations. You can't do locking because you don't have access to both databases, and you can't do merge checking without adding an extra step.

I'm not a database designer/admin though, and I reckon one of those would be better qualified to help you out.

adam

nahdoic

Can a model like that work?

If someone changes an entry in the local db, then someone changes that same entry on the remote db. When it comes to synchronising, how does it now which entry should take precedence? Actually ... you could just use a TIMESTAMP field. Whatever entry had been changed most recently would take precedence.

So whenver the company connects to the net, a simple little program on the local computer would update the remote db with all updated/added entries from the local db since the program last ran. BUT if it comes across an entry on the remote db with a more recent TIMESTAMP than what the one the local db has, it knows "uh-oh this has been changed". So instead it goes and updates its own local db with this latest entry and leaves the remote db the way it is.

Put the same basic issue still potentially exists. What if someone changed the local db with some real important new information. And someone was just messing around on the remote db, updating an entry, then going, oh no, i didn't want to do that. And puts it back to what it was. But now the timestamp has been updated, so when the local program runs it won't update the remote db with this new information. Worse still it will actually take the old remote db entry, and update the local db with the old data!

It really does come down to, who do you want to have the final say? The local or remote db? I don't think you can design a perfect system for something like this. As you know yourself, in a perfect world, data really should only be stored once (or else major headaches ensue)

Terminator

Sounds like a nightmare to implement. Nahdoic mentioned some interesting obstacles which would be 'difficult' to overcome.

Ideally in this situation you should put the whole thing online and let everyone connect to the dbase that way. If its a big company then they could easily afford DSL.

Ronin

Ken,

You need mysql 4, they plan to have database replication by default..but its still in beta .... and no playing has been done yet..

maybe ya might look at that..i'll probs be having a look at the beta soon enough...

Paul.

Kali

thanks for the info lads, i've been wrecking my head over it for the past three or four days, just wondering if anyone would come up with an idea or way of looking at it that i had overlooked
had thought of dahamastas storing query solution alright, but hard to implement, using a timestamp would be easier alright for smaller databases, but with several dozen tables things could get messy...

either way i would have been making a recomendation that if a web database was to be used then that would become the master db, and therefore a faster connection/fixed line would be a priority, thing is the company is basically a NPO, so expensive options aren't options full stop

anyway another db-related question while your all on a roll..

the company needs to distribute disks to ppl around the country who then edit their own specific data and send it back up to the main office (the web-interface would have done away with the need for this), obviously (im thinking anyway) the best bet for something like this would be a small vb program storing data in either text or a small db format...
which would basically mean msql/asp would be a (much) better and easier option than php/mysql as the main db.
so any thoughts on the importing/exporting of data in that way? i cant immediately see a solution for php/mysql.. which led me to the alternative, hell im gonna have loads of fun with this contract

dahamsta

Kali, I know it's a plain-as-the-nose-on-your-face suggestion, but the best place to ask about this is probably the MySQL mailing list. Make sure you search the archives first.

adam

sisob

I dont have a clue - but here goes

1. you could set up a webserver on the net that would host a page that would use your isdn line to contact the sql database?

2. you could have a read-only version of the database, updated regularly, on the server mentioned above? this would cut down on the traffic going over the isdn.

I think that some solution like that is the only way - having 2 writable databases will invarioubly cause data loss.

upgrading the isdn line is the way to go IMHO

Terminator

as the advert goes, your client needs IBM - not sure they can afford them though

Gavin

is the isdn line dedicated ? ( I assume it must be seeing as you are gonna have to sync the db's)

But my idea was this... Have the one database. On the internet machine have the database and apache/php frontend.

In the office have the apache/php frontend just connecting to the database on the internet.

Seeing as it's just db queries, with decent sql used to cut down on processing on the php side the bandwith required would not be very high..

perhaps use postgresql and use inbuilt functions to cut down on traffic even more. And cause it's isdn the latency won't be too bad.

Gav

DJB

If your going in this route you would most likely be better off putting everything on the web and using a custom built control panel to control the database as they would offline.

Have a look at www.site-manager.com for more information on the work that we do in this field. Some work includes a system for Irish Ferries for the sailing update information... www.irishferries.com.

Contact me through the site if you want any more info.

Regards,

smiles

ColdFusion is a database-to-Web gateway that's turning into a very popular tool for businesses that need to get lots and lots of data on the Web lickety-split.

just looking at this tuff and thought it might help you...

http://hotwired.lycos.com/webmonkey/99/03/index1a.html

Bit more info there.

<< Fio >>