Systems and methods for managing web, mobile, and point-of-sale servers with a customer relations management system
Methods and systems are described for managing business interfaces including web sites, e-commerce platforms, mobile apps, and point-of-sale (POS) systems using databases and user interfaces on a customer relations management (CRM) system. Performance can be improved using two-way synchronization between the CRM database and a second database used by the business interface. Latency and efficiency can be improved by performing the synchronization using a message queue implemented with a single database table that can be replicated using a native replication mechanism. Multiple CRM databases can synchronize to a single second database organized into multiple tenants. Detection of changes in either database can be implemented using polling or change triggers.
1. A method for synchronizing a first database and a second database comprising:
storing the first database in a first memory of a first computing device, and the second database in a second memory of a second computing device;
detecting a first update to the first database;
creating, based on the detecting, a first message queue item;
inserting the first message queue item into a first message queue database on the first computing device;
replicating the first message queue item from the first message queue database to a second message queue item in a second message queue database on the second computing device;
selecting the second message queue item from the second message queue database; and
updating, based on the selecting, the second database to correspond to the first update;
wherein the replicating of the first message queue item comprises:
detecting the insertion of the first message queue item into the first message queue database;
creating a first queue update object representing the insertion of the first message queue item into the first message queue database;
transmitting the first queue update object to the second message queue database; and
applying the first queue update object to the second message queue database to insert the second message queue item into the second message queue database;
wherein the selecting of the second message queue item from the second message queue database comprises:
detecting the presence of the second message queue item; and
removing the second message queue item from the second message queue database; and
wherein the updating of the second database comprises:
creating a first database update object representing the first update to the first database; and
applying the first database update object to the second database;
wherein the first database update object represents an action selected from the group consisting of a change to a value in the first database, an insertion of a first record into the first database, a deletion of a second record from the first database, an addition to a schema of the first database, and a deletion from the schema of the first database.
2. The method of claim 1 , further comprising:
detecting a second update to the second database;
creating, based on the detecting of the second update, a third message queue item;
inserting the third message queue item into the second message queue database;
replicating the third message queue item from the second message queue database to a fourth message queue item in the first message queue database;
selecting the fourth message queue item from the first message queue database; and
updating, based on the selecting of the fourth message queue item, the first database to correspond to the second update.
3. The method of claim 2 , wherein the replicating of the third message queue item comprises:
detecting the insertion of the third message queue item into the second message queue database;
creating a second queue update object representing the insertion of the third message queue item into the second message queue database;
transmitting the second queue update object to the first message queue database; and
applying the second queue update object to the first message queue database to insert the fourth message queue item into the first message queue database;
wherein the selecting of the fourth message queue item from the first message queue database comprises:
detecting the presence of the fourth message queue item; and
removing the fourth message queue item from the first message queue database; and
wherein the updating of the first database comprises:
creating a second database update object representing the second update to the second database; and
applying the second database update object to the first database.
4. The method of claim 3 , wherein the second database update object represents an action selected from the group consisting of a change to a value in the second database, an insertion of a first record into the second database, a deletion of a second record from the second database, an addition to a schema of the second database, and a deletion from the schema of the second database.
5. The method of claim 1 , wherein:
the detecting of the first update comprises receiving a trigger based on an external input; and
the updating of the second database comprises a synchronization of the first database and the second database.
6. The method of claim 2 , wherein:
the detecting of the second update comprises receiving a trigger based on an external input; and
the updating of the first database comprises a synchronization of the first database and the second database.
7. The method of claim 1 , further comprising:
accessing the first database by a customer relationship management system; and
accessing the second database by an entity selected from the group consisting of an e-commerce system and a point-of-sale system.
8. The method of claim 1 , wherein the detecting comprises:
a first polling of the first database;
a second polling of the first database; and
detecting, based on a difference between the first polling and the second polling, the first update.
9. The method of claim 1 , wherein the detecting comprises:
receiving, based on an access to the first database, a trigger;
determining, based on the trigger, a change to the first database; and
detecting, based on the determination, the first update.
10. A system for synchronizing a first database and a second database comprising:
a first computing device including a first processor and a first memory, and a second computing device including a second processor and a second memory, the first and second computing devices configured to:
store the first database in the first memory and the second database in the second memory;
detect a first update to the first database;
create, based on the detecting, a first message queue item;
insert the first message queue item into a first message queue database on the first computing device;
replicate the first message queue item from the first message queue database to a second message queue item in a second message queue database on the second computing device;
select the second message queue item from the second message queue database; and
update, based on the selecting, the second database to correspond to the first update;
wherein the replicating of the first message queue item comprises:
detecting the insertion of the first message queue item into the first message queue database;
creating a first queue update object representing the insertion of the first message queue item into the first message queue database;
transmitting the first queue update object to the second message queue database; and
applying the first queue update object to the second message queue database to insert the second message queue item into the second message queue database;
wherein the selecting of the second message queue item from the second message queue database comprises:
detecting the presence of the second message queue item; and
removing the second message queue item from the second message queue database; and
wherein the updating of the second database comprises:
creating a first database update object representing the first update to the first database; and
applying the first database update object to the second database;
wherein the first database update object represents an action selected from the group consisting of a change to a value in the first database, an insertion of a first record into the first database, a deletion of a second record from the first database, an addition to a schema of the first database, and a deletion from the schema of the first database.
11. The system of claim 10 , wherein the first and second computing devices are further configured to:
detect a second update to the second database;
create, based on the detecting, a third message queue item;
insert the third message queue item into the second message queue database;
replicate the third message queue item from the second message queue database to a fourth message queue item in the first message queue database;
select the fourth message queue item from the first message queue database; and
update, based on the selecting, the first database to correspond to the second update.
12. The system of claim 11 , wherein the replicating of the third message queue item comprises:
detecting the insertion of the third message queue item into the second message queue database;
creating a second queue update object representing the insertion of the third message queue item into the second message queue database;
transmitting the second queue update object to the first message queue database; and
applying the second queue update object to the first message queue database to insert the fourth message queue item into the first message queue database;
wherein the selecting of the fourth message queue item from the first message queue database comprises:
detecting the presence of the fourth message queue item; and
removing the fourth message queue item from the first message queue database; and
wherein the updating of the first database comprises:
creating a second database update object representing the second update to the second database; and
applying the second database update object to the first database.
13. The system of claim 12 , wherein the second database update object represents an action selected from the group consisting of a change to a value in the second database, an insertion of a first record into the second database, a deletion of a second record from the second database, an addition to a schema of the second database, and a deletion from the schema of the second database.
14. The system of claim 10 , wherein:
the detecting of the first update comprises receiving a trigger based on an external input; and
the updating of the second database comprises a full synchronization of the first database and the second database.
15. The system of claim 11 , wherein:
the detecting of the second update comprises receiving a trigger based on an external input; and
the updating of the first database comprises a full synchronization of the first database and the second database.
16. The system of claim 10 , wherein the first and second computing devices are further configured to:
access the first database by a customer relationship management system; and
access the second database by an entity selected from the group consisting of an e-commerce system and a point-of-sale system.
17. The system of claim 10 , wherein the detecting comprises:
a first polling of the first database;
a second polling of the first database; and
detecting, based on a difference between the first polling and the second polling, the first update.
18. The system of claim 10 , wherein the detecting comprises:
receiving, based on an access to the first database, a trigger;
determining, based on the trigger, a change to the first database; and
detecting, based on the determination, the first update.
19. A non-transitory computer-readable medium having data stored therein representing software executable by a computer, the software including instructions for synchronizing a first database and a second database comprising:
storing the first database in a first memory of a first computing device, and the second database in a second memory of a second computing device;
detecting a first update to the first database;
creating, based on the detecting, a first message queue item;
inserting the first message queue item into a first message queue database on the first computing device;
replicating the first message queue item from the first message queue database to a second message queue item in a second message queue database on the second computing device;
selecting the second message queue item from the second message queue database; and
updating, based on the selecting, the second database to correspond to the first update;
wherein the replicating of the first message queue item comprises:
detecting the insertion of the first message queue item into the first message queue database;
creating a first queue update object representing the insertion of the first message queue item into the first message queue database;
transmitting the first queue update object to the second message queue database; and
applying the first queue update object to the second message queue database to insert the second message queue item into the second message queue database;
wherein the selecting of the second message queue item from the second message queue database comprises:
detecting the presence of the second message queue item; and
removing the second message queue item from the second message queue database; and
wherein the updating of the second database comprises:
creating a first database update object representing the first update to the first database; and
applying the first database update object to the second database;
wherein the first database update object represents an action selected from the group consisting of a change to a value in the first database, an insertion of a first record into the first database, a deletion of a second record from the first database, an addition to a schema of the first database, and a deletion from the schema of the first database.
20. The non-transitory computer-readable medium of claim 19 , the instructions further comprising:
detecting a second update to the second database;
creating, based on the detecting, a third message queue item;
inserting the third message queue item into the second message queue database;
replicating the third message queue item from the second message queue database to a fourth message queue item in the first message queue database;
selecting the fourth message queue item from the first message queue database; and
updating, based on the selecting, the first database to correspond to the second update;
wherein the replicating of the third message queue item comprises:
detecting the insertion of the third message queue item into the second message queue database;
creating a second queue update object representing the insertion of the third message queue item into the second message queue database;
transmitting the second queue update object to the first message queue database; and
applying the second queue update object to the first message queue database to insert the fourth message queue item into the first message queue database;
wherein the selecting of the fourth message queue item from the first message queue database comprises:
detecting the presence of the fourth message queue item; and
removing the fourth message queue item from the first message queue database; and
wherein the updating of the first database comprises:
creating a second database update object representing the second update to the second database; and
applying the second database update object to the first database.