MariaDB – Prevent Data Collision Due to Duplicate ID

auto-incrementduplicationmariadb

I am currently having a trouble with my Web Application. I made a Web application for a certain company. I made the app using CodeIgniter 3.

I built the database using Maria DB. For the id in each table, i am using Incremental Id for my application database for each table. I usually deploy the web app to the cloud server (sometimes the company have their own dedicated server, but sometimes haven't ). One day, there is a company that they don't want to deploy the app that i have made before to the cloud ( for the security purposes they said ).

This company wanted to deploy the app to the employee's PC personally in the office, while the pc for each employee not connected to each other ( i.e stand alone pc/personal computer/employee's Laptop ). They said, for every 5 months, they would collect all of the data from employee's personal computer to company's data center, and of course the data center are no connected to the internet. I told them that's not the good way to store their data. ( because the data will be duplicate when i am trying to merge all of the data into one, since my database id are in Incremental id ). The id would be collapse while my database are incremental ID. But, The company still kept to make the app that way, and i don't know how to solved this. Does anyone know how to solved this problem ?

They have at least 10 employees that would used this web app. According that, I have to deploy the app to the 10 PC personally.

Additional info : Each employee have their own unique id which they got from the company 

The study cases is what i describe below:

id   |  employee_id  | employee_name   |
1    | 156901010     |  emp1
2    | 156901039     |  emp2
3    | 156901019     |  emp3
4    | 156901015     |  emp4
5    | 156901009     |  emp5
6    | 156901038     |  emp6

the problem is whenever they fill the form from that application, some of the table are not stored the employee's id but the new id that come from increment id.

For example electronic_parts table. They have the attribute like below: 

| id  |  electronic_part_name  |  kind_of electronic_part  |

if the employee 1 fill the form from the web app , the table's content would like below.

| id  |  electronic_part_name  |  kind_of electronic_part  |
|  1  |   switch               |           1               |

and if the employee 2 fill the form from the web app , the table's content would like below. 

| id  |  electronic_part_name  |  kind_of electronic_part  |
|  1  |      duct tape         |           10              |

When i tried to merge the contents of the table into the data center it would falling apart because the duplicate id.

Best Answer

A few options: Allocate different ID ranges for each user, use composite primary keys, use UUIDs or use some of the mechanism that enables the auto_increment feature to be used in master-master replication:

  1. You can specify a different AUTO_INCREMENT position when you create the tables for each individual employee's database so that there is a range of IDs allocated for each employee. E.g. 

    CREATE TABLE electronic_parts (
  id INT USIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  electronic_part_name VARCHAR(40), 
  kind_of_electronic_part INT UNSIGNED
) AUTO_INCREMENT=1000;

This is probably not a good idea as there is nothing to prevent over-zealous employees from creating so many records that they end up in someone else's range. (Though I suppose you could build a constraint into your application to avoid such collisions.)

  1. Re-design the primary keys so that they become composite keys with one auto-incrementing key as you have already and one key that is unique for each employee. E.g..: 

    CREATE TABLE electronic_parts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  employee_id INT UNSIGNED NOT NULL, 
  electronic_part_name VARCHAR(40), 
  kind_of_electronic_part INT UNSIGNED,
  PRIMARY KEY (id, employee_id) 
);

    You application then has to populate the employee_id with a unique value for each employee. E.g.:

    INSERT INTO electronic_parts (
  employee_id, electronic_part_name, kind_of_electronic_part
) 
VALUES (156901010, 'switch', 1);

    Thanks to the auto-incrementing id component of the primary key, you don't have to worry about populating that.

Once it's time to merge all the databases, all will be fine because the primary keys are composites with one component unique for each employee, and one component unique within each individual database, so together this makes each primary key value unique across all databases.

I've tested this successfully in MariaDB 10.2.

  1. Use the UUID() or UUID_SHORT() functions to generate globally unique primary keys instead of auto incrementing primary keys. Your application then needs to either explicitly call one of these functions when creating the records, or you could use a trigger. (I personally think it's bad design to use triggers this way, but it does work.)

  2. Assuming you know a fixed upper limit to how many users there will be (n): You can use auto incrementing primary keys and set auto_increment_increment to n, and then set auto_increment_offset to 0 for the first user, 1 for the second user, 2 for the third user, and so on. This way you will avoid collisions in the same way that auto_increment is used in Galera clusters and other master-master replication configurations.

Related Question