I understand this kind of question continues to be requested before, however i couldn't locate one that in comparison the choices I are thinking about. So I will publish them here, please publish links if you will find replicates.
It has wound up an extremely lengthy publish, for those who have time please see clearly through as now you ask , in the finish
EDIT2: I have recognized a solution when i think that'll be the very best solution for the time being. However I thought I must two other questions that answer my query about concatenating amounts. They may be found here: Combine two integers to create a unique number &lifier Concatenate integers in C#. Basically would try encoding the amount (as below like 51122222) I believe this is helpful. Though maybe simply using something similar to String.Format in c# could be fast enough for my small application.
I am presently looking for a method to setup distributed programs which use exactly the same database schema and may synchronise with maybe one master database that others also sync with.
This program I'm planning presently will begin like a quite simple program to trace information. The very first version might contain two tables: Products and ItemHistory. It is really an illustration of possible fields:
ItemHistoryID (PK) ?
ItemID (FK) ?
I have listed the area title and kind, this is all about what for the PK types so that they are missing.
The very first version is a standard desktop application, I am presently thinking about using C# having a WPF front-end and SQLite for that database. Eventually I should also produce a version to operate on my small Android phone too. This is when the distributed part is available in. I do not also have an indication same goes with require the application to operate offline and permit synchronisation when online again.
Listed here are the minds I've to date on how to approach the ID's:
- Make use of a UUID for that IDs so you will find no merge conflicts
- Make use of a auto increment area and hang the beginning number on each version from the application in certain increment, e.g. 1 for first application, 10000 for second, 20000 for third etc
- Make use of a auto increment area by having an offset value to prevent conflicts with no large gaps between amounts (mysql has auto_increment_increment and auto_increment_offset with this)
- Generate my very own ID that encodes an ID for every database to allow them to get their own auto increment value and never result in a conflict. I discovered another person which had exactly the same idea: What data type is recommended for ID columns?
While option 1 works and I have tried personally it previously I wish to consider the chance of other available choices to prevent the problems with UUIDs. I must possess a solution that's simpler to see while debugging and it is sortable.
Option 2 works however it does pressure a restriction on the amount of records. I understand during my small application it'll rarely review that lots of but I must try to find out if there is a solution that doesn't require this type of limit. Option 3 eliminates the limit by utilizing alternating amounts, however i think you should know the number of database were designed or you will fill all amounts otherwise. Utilizing a oncoming of 1 and increment of just one on DB1 and begin of two and increment of two on DB2 would use every number alternatively. You could utilize 50 because the increment however you've just got another limit however its on the amount of programs that may utilize it. Again I understand its a restriction that's not really hit during my situation but happens to be an problem within an application that all of a sudden becomes extremely popular.
Option 4 appears enjoy it could solve the problem for me personally, but I am unsure whether it works used or otherwise. One idea I'd ended up being to allow a prefix to become set on each application then that may be combined with a car incrementing value. e.g. PC1, PC2 for records on the pc and perhaps PHONE1, PHONE2 etc for records in the Android. This could work but using amounts in strings causes the sorting problem with 1, 11, 100 turning up alongside one another, that's in less leading zeros are utilized after which its to a restricted quantity of records again.
I've wondered if it might be possible to utilize a number for that DB ID and also the auto increment. e.g PC = 1 and make contact with = 2. only then do we have 11, 12, 13 etc for that PC with maybe 111 for that eleventh record and 2304 for that 304th record on PHONE. But I'm not sure how this is done or maybe it may be easily done and never cause excess expenses for producing values.
At the office they've used an identical numbering system, they will use something similar to this 51122222. 5 would make reference to the demonstration of the applying, then it's a couple digit year and lastly a auto incrementing number. I have not got a obvious answer yet what goes on when we review 99999 records each year. I believe they may have figured that it is not going to take place and therefore are happy they've calculated the danger.
So finally an issue, it is possible to way to produce a primary key system for any distributed application that enables for sorting and doesn't enforce limits (besides how big the information type itself e.g. max integer)?
EDIT: Here's a bit more information on the application I intend to write. I wish to create something which will allow me to store nearly any kind of information which i might gain, the machine includes a chance to tag the records in order to explore a subject. Kinds of information I see to date might be tips about books, dvd disks, websites etc. Or possibly local strategies for the area I am living. One overall idea would be to stop keeping these items of information spread across multiple computer systems/laptops/phones in various formats.