Local database and website database

Forums › IT Pro and developers › Local database and website database - how best to sync?

hairy1

2952 posts

Uber Geek

Trusted

Lifetime subscriber

#114923 7-Mar-2013 13:33

Hi,

We are about to embark on an integration project between a Filemaker database and a website. The Filemaker database is all built and is being used on a daily basis. The website will have a mysql database.

The plan is to have the mysql database separate from the Filemaker database for:

1. Security
2. Only a subset of information is on the website database
3. Ensure the Filemaker database is fast to use
4. Retain offline database capabilities should connectivity be lost.

So, if information is updated in the MySQL database it will be pushed to the Filemaker database and visa versa.

Anyone see any downfalls with this plan? Is this how it is done in the real world with enterprise systems? It could all be built with a single MySQL database but we don't really want to go down that track given the issues outlined above.

Cheers, Matt.

My views (except when I am looking out their windows) are not those of my employer.

timmmay

16173 posts

Uber Geek

Trusted

Subscriber

#776257 7-Mar-2013 14:30

In an enterprise system you would probably have a single database to reduce complexity and costs. You could probably write a little problem (or find one) that extracts information from Filemaker and inserts/updates MySQL. Your first three issues don't look like real issues to me, they look like lack of experience and knowledge. Re offline access, you can always have a local MySQL database as well as a hosted database. Internet access doesn't go down much these days.

If you set up MySQL and your application correctly then security isn't an issue, probably less of an issue than synchronisation.

Ragnor

8035 posts

Uber Geek

Trusted

#776311 7-Mar-2013 15:28

Unless your web DB is read only or your local and web DB write to completely different tables/row I wouldn't recommend it.

Two way sync is hard.. resolving conflicts when the same exact row is changed in both DB's at the same time is nasty.

sidefx

3463 posts

Uber Geek

Trusted

#776313 7-Mar-2013 15:31

timmmay:You could probably write a little problem (or find one) that extracts information from Filemaker and inserts/updates MySQL.

Yes, or a not-so-little problem, if you decide to go down this route :D

"I was born not knowing and have had only a little time to change that here and there." | Electric Kiwi | Sharesies
- Richard Feynman

nunz

1423 posts

Uber Geek
Inactive user

#786362 24-Mar-2013 19:36

hairy1: Hi,

2. Only a subset of information is on the website database
3. Ensure the Filemaker database is fast to use
4. Retain offline database capabilities should connectivity be lost.

Cheers, Matt.

Its kind of hard to define a plan without sitting down and doing a spec of what you need. For instance - are you updating records or appending new records? do the records need to be balanced across both Dbs with the exact same keys and are the keys sequential / automatically generated etc?

Are there date fields to ensure no conflict records etc?

As for synching data in and out - it depends on how automated / human driven you want it to be.

Using an SOA architecture to transfer stuff around might help or else look at an intermediate staging database with replicating features (such as mysql both on the web and local ) and then import / export stuff from the staging DB.

Security can be achieved with automated systems and web services etc but it depends on a whole range or variables in your systems.

Using xml export on both sides, manually moving the files to and from the web / local and importing them might work and leaves you not exposing yourself to an permanently open connection to the web but it depends on how immediate the updates need to be. A replicating system every x minutes is a different process to write than an immediate mirrored set of records.

Using services / agents / and keeping only a local DB:

Security can be maintained by using services to get / put data onto the local DB.
Sanitizing the results properly gets rid of most exploit vectors.
Speed of the filemaker DB shouldn't be an issue unless you have a hugely intensive drain on the web and local. If it is that heavy then you should be considering more hardware or a better DBMS anyway.

Shane

hairy1

2952 posts

Uber Geek

Trusted

Lifetime subscriber

#786431 24-Mar-2013 21:55

Hey foks,

Thanks for the input. After actually sitting down and have a decent think about what was required the following is going to be implemented:

1. Filemaker will be the master database and any changes are replicated in the Mysql database
2. An additional Mysql table will take any changes that are required from the website (which will be minimal) and will be vetted by the filemaker database users before being inserted into the filemaker database.

It should work ok. The prime concern was to make sure that the web database was separate from the Filemaker database. There is no real time updating of both databases and the website database will have minimal input into the filemaker database (if this makes sense at 10 pm on a Sunday night!)

Cheers, Matt.

My views (except when I am looking out their windows) are not those of my employer.