Technology

Database Replication Between Heterogeneous Systems

How do you synchronize data between a central database and dozens of distributed stations - when connections are unstable, IDs can collide, and referential integrity must be maintained?

The Challenge

Unstable Connections

Remote locations like gas stations or retail branches often have unreliable internet connections. The system must work offline and sync automatically when reconnected.

Different Databases

The central office uses Firebird (robust, proven), stations use SQLite (lightweight, embedded). Both have different data types, SQL dialects, and transaction models.

ID Conflicts

When Station A creates a new receipt with ID 1000 and Station B does the same simultaneously, collisions occur. Each station needs its own ID space with central mapping.

Referential Integrity

A receipt references a customer, an article, an employee. If the receipt arrives before the customer record, the foreign key constraint fails.

Our Solution: Event-Based Replication

Instead of copying the entire database, we track only changes. Every INSERT, UPDATE, or DELETE operation is captured in a change log and transferred asynchronously to the other side.

Central DB Firebird
Master Data
Transactions
Stations SQLite

Core Concepts

1 Change Data Capture

Database triggers automatically capture every change. For each table, we log:

  • Operation - INSERT, UPDATE, or DELETE
  • Table - Which entity is affected
  • Primary Key - Which record
  • Timestamp - When the change occurred

2 ID Mapping

Each station has its own ID space. A central mapping links local IDs to global ones:

Table Station ID Central ID
Receipt100058723
Receipt100158724
Payment50091234

During transfer, all IDs - including foreign keys - are automatically remapped.

3 Three-Phase Processing

FK dependencies require a special sequence. Our algorithm processes changes in three phases:

Phase 1

Normal Operations

Execute all INSERTs/UPDATEs/DELETEs. Failed ones are buffered.

Phase 2

Retry

Retry buffered operations - the FK target may exist now.

Phase 3

Final Updates

Update FK columns that were temporarily set to NULL in phase 1.

4 MQTT as Transport Layer

For communication with offline-capable stations, we use MQTT (Message Queuing Telemetry Transport):

  • Lightweight - minimal overhead even on poor connections
  • Publish/Subscribe - decoupled communication
  • QoS levels - guaranteed delivery even after disconnection

Real-World Example: Gas Station Network

A gas station operator with 15 locations needs real-time data at headquarters, but stations must remain fully functional even during internet outages.

Central → Station

  • Product master data (prices, descriptions)
  • Customer data (companies, credit limits)
  • Fuel cards and permissions
  • Price adjustments and discounts

Station → Central

  • Fuel receipts and payments
  • Invoices and delivery notes
  • Loyalty card transactions
  • End-of-day reports

Results

100%

Offline Capability

<5s

Sync Latency (online)

0

Data Loss

Facing a Similar Challenge?

We develop custom replication solutions for your requirements - from simple master-slave replication to complex multi-master scenarios.

Schedule a Consultation