How to create a primary key when online and MySQL databases need to be synchronized
I am developing an inventory software with MySQL and PHP where the local database will sync with the online database.
Suppose I have a sell table and sell_id is the primary key of the table. I usually use INT and auto increment with a primary key.
In table, local database 1 has a table with two entries (sell_id 1,2) and local database 2 , which has 2 entries (sell_id 1,2).
If I sync / insert these 2 local sales table tables into the online sales table it becomes (sell_id 1,2,3,4).
When a sale ID changes, it affects those records in another table that use sell_id as a foreign key.
How should I plan to create a primary key in this situation.
I am planning on using an alphanumeric ID that will be unique across both databases. Will this create any problems or slow down my db request for millions of sell_id?
Are there other ways to solve the problem?
source to share
It's too long for a comment.
Often times, when you have a replicated system, the goal is to maintain the same data across all servers. This does not appear to be your business requirement.
Instead, you might consider having a composite primary key on all servers. This will combine the auto-append primary key with the server id. All tables referencing a foreign key will need to include a server column as well as an id.
In general, I am not a fan of composite primary keys. However, you have a distributed database and need to define the specific section of the database that contains the data. This looks like a good use case for composite primary keys.
An alternative approach - if you want to take the risk - is to set the auto-numbering to a different seed on each server. Use a large int and a large value, for example 1,000,000,000,000 for one server, 2,000,000,000,000 for the next, and so on. I prefer that "server" is explicitly represented as a column.
source to share