How do i prevent the duplicate id from the imported table in MariaDB?

Question

(Before that, i apologize for my bad English) I have study cases like this:

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 Auto-increment 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 column id for every table are in auto-increment id, and it's a primary key). Unfortunately, The company still want to kept the app that way, and i don't know how to solved this.

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, and i made the auto_increment id for each employee, just like the table 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_id  |

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

| id  |  electronic_part_name  |  kind_of_electronic_part_id  |
|  1  |   switch               |           1               |

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

| id  |  electronic_part_name  |  kind_of_electronic_part_id  |
|  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.

It's getting worst when i think about my foreign key in other tables.. like for example the customer_order table.

The table for customer_order column looks like below (just a sample, not the actual table, but similar).

|id  |  customer_name   |  electronic_parts_id  |  cashier(a.k.a employee_id, the increment id one, not the id that employee got from a company as i described above )  |
| 1  |   Henry          |           1           |              10              |
| 2  |   Julie          |           2           |              9               |

Does anyone know how to solved this problem ? or can someone suggest/recommend me some good way to solved this ?

NOTE: Each Employees have their own database for their app, so the database is not centralized, it's a stand-alone database, that means, i have to installed the database to the employee's pc one by one

The only solution is to use at least one pc as a database server.... — Hackerman, Jan 24 '18 at 17:56
@Hackerman the company didnt want to... he want the app for each personal conputer... — Gagantous, Jan 24 '18 at 22:24
It's not a viable solution, we don't live in the sixties.... — Hackerman, Jan 25 '18 at 01:22
indeed, that's correct. but i have no power to persuade them to follow my solution :'( @Hackerman — Gagantous, Jan 25 '18 at 01:25
When "merging" add the employeeid to the rows. So every table in the merged data will have an added column for employeeid. *It sucks as a solution however.* — Paul Maxwell, Jan 25 '18 at 03:04

Deepu S Nath · Accepted Answer · 2018-02-01T16:56:54.023

This is an unconventional situation and you can have an unconventional solution.

I can suggest you two methods to solve this issue.

Instead of using autoincrement for primary key generate a UUID and use it as the primary key. Regarding the probability of duplicates in random UUIDs: Only after generating 1 billion UUIDs every second for the next 100 years

In CodeIgniter you could do this with the following code snippet.
```
$this->db->set('id', 'UUID', FALSE);
```
This generates a 36 characters hexadecimal key (with 4 dashes included).
```
ac689561-f7c9-4f7e-be94-33c6c0fb0672
```
As you can see it has dashes in the string, using the CodeIgniter DB function will insert this in the database with the dashes, it still will work. If it does not look at clean, you could remove and convert the string to a 32-char key.

You can use the following function with the help of [CodeIgniter UUID library][1].
```
function uuid_key {
        $this->load->library('uuid');
        //Output a v4 UUID 
        $id = $this->uuid->v4();
        $id = str_replace('-', '', $id);
        $this->db->set('id', $id, FALSE);
}
```
Now we have a 32-byte key,
```
ac689561f7c94f7ebe9433c6c0fb0672
```
An alternate unconventional method to tackle the situation is by adding function to log all Insert, Update, Delete queries processed in the site to a file locally. By this way, in each local implementation will generate a log file with an actual list of queries that modify the DB over time in the right sequential order.

At any point in time, the state of the database is the result of the set of all those queries happened in the past till that date.

So in every 5 months when you are ready to collect data from employees personal computer, instead of taking data dump, take this file with all query log.(Note: Such a query log won't have auto-increment id as it will be created only in the real time when it is executed towards a Database. )

Use such files to import data to your datacenter. This will not conflict as it will generate autoincrements in your data center in real time. (Hope you do not have to link your local to data center at any point of time in future)

[1]: https://github.com/Repox/codeigniter-uuid

Do you know the exact number of How many percentage for duplicate id for the fist solution ? It has 32 char long... well that means it has 32 root 32 combination ? — Gagantous, Feb 02 '18 at 00:13
i am not quite understand the second solution... could you explain more ? — Gagantous, Feb 02 '18 at 14:05
Regarding the possibility of the duplicate for UUID is being discussed in the following thread. https://stackoverflow.com/questions/1155008/how-unique-is-uuid — Deepu S Nath, Feb 05 '18 at 15:36
The second solution is to simply add code to open a file and log the queries with runtime data. You have to implement this code in every section where any query gets executed. Even though it is just a possible method, I suggest you go with the first solution. — Deepu S Nath, Feb 05 '18 at 15:44

Rick James · Answer 2 · 2018-01-26T18:24:34.567

Is that id used in any other tables? It would probably be involved in a JOIN. If so, you have a big problem of unraveling the ids.

If the id is not used anywhere else, then the values are irrelevant, and the rows can be renumbered. This would be done (roughly speaking) by loading the data from the various sources into the same table, but not include the id in the load.

Or, if there is some other column (or combination of columns) that is UNIQUE, then make that the PRIMARY KEY and get rid of id.

Which case applies? We can pursue in more detail. Please provide SHOW CREATE TABLE for any table(s) that are relevant.

In my first case (where id is used as a FK elsewhere), do something like this:

While inserting the rows into the table with id, increment the values by enough to avoid colliding with the existing ids. Then do (in the same transaction):

UPDATE the_other_table SET fk_id = fk_id + same_increment.

Repeat for each other table and each id, as needed.

Yes,the `electronic_parts`'s `id` is needed for other table as foreign key, for example, `customer_order` table. it's need `electronic_parts`'s `id`, to join the `electronic_parts`'s `id` as a foreign key in `customer_order` table with primary key in `electronic_part`'s `id` — Gagantous, Jan 26 '18 at 17:00

score 2 · Answer 3 · answered Jan 26 '18 at 16:50

2

I think your problem come from your database... you didn't design it well. it's a bug if you have an id for two difference users .

if you just made your id field unique in your database then two employee wouldn't have a same id so your problem is in your table design .

just initiate your id field like this and your problem will be solved .

    CREATE TABLE [YOUR TABLE NAME](
        [ID]  int   NOT NULL    IDENTITY(1,1)   PRIMARY KEY,
....

answered Jan 26 '18 at 16:50

A Farmanbar

4,381
5
24
42

so we are not using the auto increment id ? so you mean, we populate our own unique id from the application, correct ? – Gagantous Jan 26 '18 at 17:17
@Gagantous you must define your table as what i said here – A Farmanbar Jan 26 '18 at 20:12

score 0 · Answer 4 · answered Feb 02 '18 at 06:06

Is it required for the id to be an integer? if not may be you can use a prefix on the id so the input for each employee will be unique in general. that means you have to give up the auto increment and just do count on the table data (assuming youre not deleting any of the records.)

score 0 · Answer 5 · answered Feb 02 '18 at 08:57

0

You may need to write a code in PHP to handel this. If other table is already following unique/primary key based than it is fine.

You can also do it after import. like this Find duplicates in the same table in MySQL

answered Feb 02 '18 at 08:57

deep-raptorx

1
2

Welcome to StackOverflow! When you provide answers, try and be specific in terms of code. Answers that contain links and general advice like "you may need to write code" will not resolve the question. This question isn't short and to the point, so it may be necessary to break the question down, either in your answer or as a prerequisite to answering. In this case, the real problem seems to be about database modelling, so an ideal answer addresses what good modelling looks like. – sshine Feb 02 '18 at 09:15

How do i prevent the duplicate id from the imported table in MariaDB?

5 Answers5