UUDI/GUID primary key in MySQL

1
votes

We have an app (IONIC 2 phone app front-end, ASP .Net Core 2.0 Web Api backend with MySql Server).

The phone app needs to be able to work in offline mode, and be able to sync (up and down) when it has internet connection. I immediately realized the auto-incrementing int primary keys in the MySql database would be an issue, and thought about using GUID/UUID primary keys instead. But I am worried about performance. I have two questions:

  1. What is the best way to store a UUID field in MySql? CHAR(36)? As far as I can see, MySql doesn't have a dedicated UUID data type? (I am using MySql Server 5.6.30)

  2. Would it be a good idea to NOT make it a Primary Key, so that it's not UNIQUE? Surely this would speed up performance? And a UUID is almost guaranteed to be unique anyway, so there's no need for MySql Server to waste time checking for this...

Thanks

1
mysqlprimary-keyuuidguid
I don't follow the problem or why you have come down to a choice between UUID or an auto increment primary key. What would be the issue with just allowing MySQL to assign an id when the app finally gets online and does a sync? – Tim Biegeleisen
Thanks for the quick reply Tim. Well, the phone apps will have a local database where they will store all data while working in offline mode. If we are using an auto-incrementing int primary key, and the user of the phone app creates a new record, it might get ID = 157 (and this will be used in all other tables that have a foreign key to this table). Now, when the phone is online and the app syncs, on the live database it might get a different ID (because other users have synced their new records in the meantime). Now there is a mismatch between the IDs on the phone's db and on the live db – Fabricio Rodriguez
Yes, when the phone app syncs, the Web API could tell the phone app what the ACTUAL id's are, and the phone app could then update it's ID in all it's tables. But this could be an issue of the Web API tell the phone app "right, the actual ID for that record is 160", but the phone app already has that ID in use (because they guy added 4 new records while working offline) – Fabricio Rodriguez
I guess I don't see the issue of having two IDs. The master database uses auto increment, and each app has its own ID. Nothing wrong with this from the point of view of other apps; those other apps cannot see the new item in the master catalog anyway. – Tim Biegeleisen
hmmm.... ok, so there would be some sort of mapping between the client id's and the master id's? – Fabricio Rodriguez

1 Answers

1
votes

Without having the full details I would suggest the following general workflow:

  • a user adds a new product
  • this results in a record being written to the local SQLite database
  • maintain a column called sync which keeps track of whether a local record has been pushed to the server
  • note that the products which the app displays is the sum of cached server data and SQLite data which has not yet been synched
  • when the app does a sync, it pushes new products first, then marks those SQLite records as having been synched
  • when it pulls the latest catalog, it will return the new products with their actual unique IDs as they appear in the remote MySQL database
  • if the app goes offline again, then just go back to the first step and do the same thing


The basic idea here is to just focus on keeping one version of the truth as much as possible. Don't task your app with assigning an ID to a product which the server knows nothing about. For one thing, would it be meaningful to assign such an ID and then the user decides to delete the product without ever synching?