Table with primary key that never repeats

Question

Table with primary key that never repeats

Navigation

#1 by (1 votes)

1

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

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

What happens is this: let's say that PC1 and PC2 write data to 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 in their local banks, and will be generated confusion with the IDs. Ex:

on PC1 product4 will have ID 4

on PC2 product1 will have ID 4

My question is: Is it possible to generate a non-recurring ID?

mysql modelagem

asked by anonymous 17.04.2017 / 21:20

1 answer

Playing parameter in url Accessing a pointer pointer (c)

score 1 · Accepted Answer

The default solution for distributed banks is the use of GUIDs. Some people do not like it. I will not go into detail because this has already been extensively answered here:

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

Consider binary encoding of this code saving space.

A not much needed optimization is to write with INT or even BIGINT if you think that one day you will have millions of lines on each machine. The code would be calculated according to the customer code. Let's say you wanted to provision up to 1,000 clients, so the code will always be an incremental number plus the client's 1 million times, so you'll have room for 1 million rows in each row.