CODEWITHSUNDEEP
mysqlprimary-keyuuidguid
Fabricio Rodriguez
Fabricio Rodriguez

Reputation: 4239

UUDI/GUID primary key in MySQL

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

Upvotes: 1

Views: 942

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

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?

Upvotes: 1

Related Questions