Optimum setup for a mobile practice with admin assistant
Any suggestions about how to set up a system to meet the following needs:
- mobile vet with unreliable internet connection from car
- administrative assistant wants to be able to do clerical type things while vet is out and about making house calls
1. is easy - install OpenVPMS on laptop
2. is significantly more difficult, but I think possible using master-master replication - see for example http://www.howtoforge.com/mysql_master_master_replication
However, the mobile vet is not very technically literate, and suggesting master-master synchronisation worries me.
Has anyone tried more low tech solutions (eg mobile vet comes home and copies database to assistant's machine). This gives the assistant access to the data - but functionally they cannot do anything that will update the database because any changes will be lost when the database is re-copied from the vet's laptop.
Regards, Tim G
Re: Optimum setup for a mobile practice with admin assistant
Master Master wouldnt work. in that scenario it is assumed that when one master is unavailable its because its down - all to do with High availabilty..the problem you have is that with the mobile vet is out....he is still transacting as is the admin assitant back at base...thus you will get primary key errors when they both come back into range and try and sync.
There has to be a priority. So I would try setting up say the home machine as the priority machine.
Then set the remote (mobile machine up)
Each morning the remote is given an exact copy of the base machines sql.
Each evening a loader script is used via kettle to load the changes from the remote to the host.
I would have a extra table like the etllog that records the last PK on each table (for those that have them)
The way you do the kettle statement is going to vary - you could compare master with the remote...anything new on the remote gets updated to the master.
Once the master is updated you drop the database on remote and copy in the new master.
One BIG issue as I see it is when you invoice of the remote it wont correspond to the invoice eventually generated on the master.
So you could reverse it and have the master as the remote pc
I need to think about it more
Re: Optimum setup for a mobile practice with admin assistant
Ben - thanks for the input. Some years ago when my son's practice was smaller, I built an application in VB that synchronised two RxWorks databases. As you might guess it was not a simple application. It actually worked with 4 databases, one called the Common Starting Point (your beginning of day one), the two end-of-day ones, and the merged output. It handled both changes to existing records and the addition of new ones.
Applying the same logic to OpenVPMS is conceptually possible, but it would be a big job - looking at the revision history it took me 9 months to get the program running reliably. I am certainly not volunteering to reimplement the logic for OpenVPMS.
Note that I was forced to do field by field comparisons to look for updates. In theory, since we have more control over OpenVPMS, it would be possible to adjust things so as to leave a log of the changes and one could use this data to do the synchronisation.
However, this is still no small project.
Regards, Tim G
Re: Optimum setup for a mobile practice with admin assistant
http://datacharmer.blogspot.com.au/2013/03/multi-master-data-conflicts-p...
might be a good place to start research for master master stuff
Re: Optimum setup for a mobile practice with admin assistant
Two way replication is a non trivial exercise, particularly when dealing with conflicts.
I think your best bet is to investigate getting a more reliable internet connection. Have you tried using an aerial to boost signal?
The fallback would be to have a recent copy of the database installed locally so that information can be recorded. The limitation is that you then need to apply these manually to the master database.
Re: Optimum setup for a mobile practice with admin assistant
Tim A/Ben - thnaks for the input. To wind up this discussion so that others coming this way will see what we concluded, extracts from two off-forum emails.
First from me: "The master-master replication has significant pitfalls - see
http://datacharmer.blogspot.com.au/2013/03/multi-master-data-conflicts-part-1.html
and
http://datacharmer.blogspot.com.au/2013/03/multi-master-data-conflicts-part-2.html
if you want to read a technical discussion on the problem.
Bottom line: this is a pig of a problem. It is easy to give the admin assistant a copy of the database at the end of the day so that they can use it for reference doing tasks like chasing up customers who have not paid and generating reports on historical data. However, the admin assistant will not be able to do any updates. (Actually they can, but the information will get lost when you refresh their database at the end of the day.)"
and then the reply: "Thanks for looking into this. I can get by without this feature. My partner is only working very part time on my stuff at the moment. I'm basically getting her to chase up bills and ring people for updates on their pets. If I give her a copy of the database when I'm out she can do the calling and will just to have make changes when I'm home."
Regards, Tim G