mysql - Is DB Replication a proper solution for this scenario/problem? -

i'm investigating wheter use of replication operation mode way of tackling following design problem. have no prior experience replication of mysql databases, keep in mind while reading.

i have develop distributed system of remote computers (tablets in specific), sharing (central) mysql database data storage. system composed central unit containing mysql database, terminal interfaces database simple quering , portable device (tablet) interfaces database throught wifi network.

this system serves management of customers, orders, sales , stock local shop selling products out home tablets. each "transaction" need syncronize tablet mysql database before exits local network, inserting necessary data taken mysql tables, describing customers, products , more.

i'm using sqlite database on tablet containing necessary data , outside activity exploits inserting new sales, update customers state etc. @ end of transiction tablet comes home , must synchronize again central unit, mysql.

how should manage data consistency across db instances? should use ad-hoc solution (customized sync'ing software logic) or, hope , expect, built-in replication facilities able tackle consistency problems of scenario?

now i'm afraid data integrity after these transactions, because other data inserted or updated before tablet's return syncronization. how ensure data integrity , consistency across every kind of operation mean of these remote tablets, periodically sync'ed?

it important point out during operation remote tablets not have connection central node (the usual case). replication/integrity operations should operated in deferred mode, i.e. when tablets connected again central node.

thanks

quick note - if i'm rambling below, i'm sorry :) please ask clarification , try.

if want replicate data, can use inbuilt functionality provided database system. however, these aimed @ creating exact copy, no provision smarts (adjusting stock levels, example).

the easiest way , allow smarts provide way handle events. event can whatever decide required, example order being placed against stock item, , has event type specify event means. importantly, event has timestamp. allows decide events relevant when synchronise device central hub. helps resolve conflicts when events generated on both sides same thing.

importantly, if can define events can occur both ends, , scope, makes easier identify , resolve these conflicts. helps decide information need resolve them, whether simple timestamp enough or sort of priority system (ie. change on main server takes precedence). also, whether need resolving @ all, or whether events can processed one-way.

when connect device, process events have occurred since last time connected. can allow customise events processed (main system or mobile device), , effects produce. importantly, keep things simple, events need generated things can change on mobile device.

from example, synchronising stock lists, customers, , other stuff straightforward replication of data mobile device, , possibly handled sort of automatic replication, though have able specify information replicate (as don't want exact copy of everything).

the mobile device generate events onsite when creating order or that. these events tell main system order has been created/cancelled/etc. include table specify products , quantities ordered/cancelled.

when sync up, mobile device uploads it's events, , main machine processes them , works out conflicts own events. allows client download relevant events might affect items contains (or may not, , repopulate device new data, depending on complexity of system).

there may other things changing customer information onsite or whatever, again straightforward data sync, though may wish create event has happened decide whether change customer info in main system (as might have been changed else...)

---

as example, company used work developed mobile system customer service representatives repairing widgets. may not live in same state main system. in morning, customer service person turn on laptop, synchronise main system , receive updates jobs day in form of events. might include new jobs, or status update saying job had been cancelled, redirected representative, rescheduled, whatever. these generated @ head office (usually) who's taking phone calls , updating system through sort of interface. didn't know interface was, produced events in database use.

when event generated specifying new job, that's did. job details, including customer , widget information, straight sync device. importantly, part lists , stuff synced 1 way, mobile device.

when turn job mark commenced, thing, ordered required parts if missing something, mark completed, whatever. happened offline, , generated events on mobile device.

at point, representative connect , sync. process events created on mobile device, working out conflicts, , allow mobile device update new events main system. importantly, conflict resolution restricted events happened when - if representative had fixed widget, cancellation of appointment irrelevant. also, if required parts, straight sync of data, telling main system order parts, not happen without associated event.