How to prevent duplicate identifier from imported table in MariaDB?

Question

(Before that, I apologize for my poor English) I have such examples:

I am currently having problems with my web application. I made a web application for a specific company. I made an application using CodeIgniter 3.

I built a database using Maria DB. For the identifier in each table, I use Auto-increment id for my application database for each table. I usually use a web application for a cloud server (sometimes the company has its own dedicated server, but sometimes not). Once there is a company in which they do not want to deploy the application that I made before in the cloud (for the security reasons they said).

This company wanted to deploy the application on a personal computer in person at the office, while the computer for each employee was not connected to each other (for example, a stand-alone PC / personal computer / laptop for employees). They said that every 5 months they collected all the data from the employee’s personal computer to the company’s data center, and, of course, the data center was not connected to the Internet. I told them that is not the best way to store my data. (because the data will be repeated when I try to combine all the data into one, since my column id for each table is in the auto increment id, and this is the primary key ). Unfortunately, the company still wants to save the application this way, and I don’t know how to solve it.

They have at least 10 employees who would use this web application. According to this, I have to deploy the application to PC 10 personally.

Additional information: each employee has a unique identifier that they received from the company, and I made an auto_increment identifier for each employee, as in 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 that whenever they fill out a form from this application, some of the tables do not store the employee identifier, but a new identifier that comes from the increment identifier.

For example, electronic_parts . They have an attribute as shown below:

 | id | electronic_part_name | kind_of_electronic_part_id | 

if emp1 fill out a form from a web application, the contents of the table will look below.

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

and if emp2 fill out a form from a web application, the contents of the table will look 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 a data center, it would fall apart because the duplicate id.

It gets worse when I think of my foreign key in other tables. For example, customer_order table.

The table for the customer_order column is as follows (just a sample, not an actual table, but similar).

 |id | customer_name | electronic_parts_id | cashier(aka 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 solve this problem? or can someone suggest / recommend me a good way to solve this?

NOTE. Each employee has his own database for his application, so the database is not centralized, it is an autonomous database, which means that I have to install the database on the employee’s PC one by one.

Answer 1

This is an unconventional situation, and you may have an unconventional solution.

I can offer you two methods to solve this problem.

• Instead of using auto-increment for the primary key, generate a UUID and use it as the primary key. Regarding the probability of duplication in random UUIDs: only after generating 1 billion UUIDs every second for the next 100 years

  In CodeIgniter, you can do this with the following code snippet.

   $this->db->set('id', 'UUID', FALSE); 

  This generates a 36-character hexadecimal key (with four dashes included).

   ac689561-f7c9-4f7e-be94-33c6c0fb0672 

  As you can see, there is a dash in the row, using the DB CodeIgniter function will insert this into the database with a dash, it will still work. If it does not look clean, you can delete and convert the string to 32-char.

  You can use the following function with [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 alternative non-traditional method of solving this situation is adding a function to register all processed requests "Insert", "Update", "Delete" on the site in a file locally. Thus, in each local implementation, it will generate a log file with an actual list of queries that modify the database over time in the correct sequential order.

  At any given time, the state of the database is the result of a set of all these queries occurred in the past before this date.

  So, every 5 months, when you are ready to collect data from a personal computer, instead of taking data, a file with the entire query log. (Note: such a query log will not have an auto-increment id, since it will only be created in real time when it is executed against the database.)

  Use these files to import data into a data center. This will not be a conflict, because it will generate auto-increments in your data center in real time. (I hope you do not need to bind your local with the data center at any point in time in the future)

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

Answer 2

Is id in any other tables? He will probably be involved in JOIN . If so, you have a big problem unraveling identifiers.

If id not used anywhere, then the values ​​do not matter, and the lines can be renumbered. This will be done (roughly) by loading data from different sources into the same table, but not including id in the load.

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

In which case? We can continue in more detail. Please provide SHOW CREATE TABLE for any tables that are relevant.

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

When inserting rows into a table with id , increase the values ​​enough to avoid colliding with existing identifiers. Then execute (in the same transaction):

 UPDATE the_other_table SET fk_id = fk_id + same_increment. 

Repeat for each table and each identifier, if necessary.

Answer 3

I think your problem comes from your database ... you have not developed it well. this is a mistake if you have an identifier for two differential users.

if you just made your id field unique in your database, then two employees will not have the same identifier, so your problem will be in the design of your table.

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

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

Answer 4

Is an id required for an integer? if not, you can use the prefix on the identifier, so the entry for each employee will be unique in general. this means that you should abandon the automatic increment and just rely on the table data (assuming that you are not deleting any records).

Answer 5

You may need to write code in PHP to pass this. If another table is already working with a unique / primary key, this is great.

You can also do this after import. how is it find duplicates in the same table in mysql