Scenario: I Created a POS (point of sale) system using mysql database. I am managing all shops data in one database. All operation was on server before but now the requirement is changed and i want to make it local too. The challenge i face is Duplicate entry for key primary
For example: The system is used by two shop. If one shop added record where id=1 in item table in his local database and the second shop also added record where id = 1 in same table in his local database. Now when i send both data to my server database, it will give me error on Duplicate entry for key primary.
Conclusion: I am not using MYSQL replication because it not suit my database structure so what will be the best solution for this issue?
You can solve this problem in many ways:
You should not sync the primary key as well from the local to remote, rather you can have some order ID (SHOPID_SOMERANDOM-NUMBER) which will be unique for shops .
Otherwise you can keep a composite key as primary key like Autoincrement_ID+SHOP_ID so that while syncing this will never be duplicate.
This shop_ID should be generated from the server at the time of installation and should not be duplicate.