Table with primary key that never repeats

Asked

Viewed 118 times

1

I have a table Produto (id, descricao, tipo) and that same table is on three different computers.

All three write data to your machine and from time to time send the data to a server.

What happens is this: let’s say PC1 and PC2 record data in the table:

PC1- 

1, produto1, tipo1
2, produto2, tipo2
3, produto3, tipo3

PC2-

1, produto4, tipo4
2, produto5, tipo5
3, produto6, tipo6

When the data is sent to the server, it will be +- like this:

1, produto1, tipo1
2, produto2, tipo2
3, produto3, tipo3
4, produto4, tipo4
5, produto5, tipo5
6, produto6, tipo6

This data will be downloaded by all other Pcs to update to their local banks, and confusion will be generated with the Ids. Ex:

in the product PC14 will have ID 4

in PC2 product will have ID 4

My question is: Is there any possibility of a ID being generated that will not be repeated?

mysql modeling
  • 1

    Tip: use a GUID

  • If at some point it will work offline there is no way. I created a unique id for each input and when updating, use this id as a reference. It’s the only way possible.

1 answer

1


The standard solution for distributed banks like this is the use of GUID. Some people don’t like it. I will not go into detail because this has already been extensively answered here:

The other option is to use a key VARCHAR with a customer code plus the incremental unique identifier. Saves space and is not a big problem. Of course, it is necessary to ensure that there will be no repetition on each machine and that there will be no two machines using the same code. If you do wrong it can create difficulties.

Consider doing a binary encoding of this code saving space.

An optimization not much needed is to record with INT or even a BIGINT if you think that one day you will have millions of lines on each machine. The code would be calculated according to the client code. Let’s say you wanted to provide up to a thousand customers, so the code will always be an incremental number plus the client code times 1 million, so it will have room for 1 million lines on each line.

Browser other questions tagged mysql modeling

You are not signed in. Login or sign up in order to post.