Reading around on the topic hasn't got me closer to understanding best practices. I'm also relatively new and self-taught in programming, so might have some questions about how to actually execute the solution.

The app I'm building requires a lot of DOM elements to be generated based on database rows. These should be updatable by the user and at the minute I'm using the auto incrementing primary key as an html attribute id.

Obviously I don't want users to modify this, or in the associated javascript object, to then affect other users' data when saved or updated. I have considered the following options:

  1. Not using auto incrementing but a uuid or guid, but I've read these are not all that advisable, especially for db performance.

  2. Concealing the client side id by hashing it when served on the server side. This also doesn't sound particularly performant.

  3. Having a 'lookup table' that is referenced and produces another, non-incremental id. Doesn't seem like a bad idea but I'm a bit unsure how best to go about generating this and how it should look.

  4. Including an extra column in the tables that would be updated that contains for example the users name and would be combined with the primary key. This seems simple enough but the idea of adding this extra column to every table that might be updated sounds redundant and not normalized, etc.

  5. Keeping the primary keys visible but producing a session table for the user that tracks what the which IDs were sent out and therefore which can be updated. New rows can be generated so long as other ones belonging to other users aren't updated.

Or, maybe I'm barking up the wrong tress here :) Thanks!

  • What's the goal? Some id that is more opaque than a simple auto_increment? – Rick James Oct 1 '18 at 23:15

UUID's make poor primary keys, but there's nothing wrong with them when used as a regular table attribute. Instead of exposing the AUTO_INCREMENT column to the client connection, adding a UUID column and exposing that should have an insignificant impact overall, and nothing that can't be addressed with a non-clustered index on the new UUID column, if necessary.

I would likely only bother adding the UUID column to the main user table and altering my update procedures to always require the user UUID as a parameter ( and might even, at that point, leave the other parameters as the current AUTO_INCREMENT value ), reinforcing the intent that a particular user can only update their own data, but there's nothing wrong with adding the UUID column to every table which may receive updates and replacing any exposed AUTO_INCREMENT values with the new UUID either.

  • UUIDs make poor primary keys? On what do you base this claim? – ypercubeᵀᴹ Sep 1 '18 at 15:37
  • @ypercubeᵀᴹ At least the fragmentation examples in the link, as well as simply using a smaller data type for indexing. Regular stuff. – Avarkx Sep 2 '18 at 12:15
  • 1
    The link is for a SQL Server question. While similar, it is not exactly the same. Second, the issues you seem to refer to, have to do with the clustering key and not the PK. – ypercubeᵀᴹ Sep 2 '18 at 14:50
  • 99% of the time your clustered index will be your PK. The 1% of the time it is not, there's still likely no actually good reason to make it a 16 byte column. Non-sequential inserts into a B-Tree is not something that is limited to SQL Server, and if you're choosing a non-incremental primary key in any situation, I would certainly hope it was at least some kind of minimal candidate key. If you believe UUID's are the best choice for a key of any kind, I'm okay with just disagreeing on that, but if there's something fundamentally wrong with my answer please point it out so i can fix it. – Avarkx Sep 2 '18 at 16:11
  • This discusses some of the performance problems of UUIDs: mysql.rjweb.org/doc.php/uuid And the performance issues apply to secondary keys as well as PRIMARY KEY, just that the table needs to get a big bigger before it hits hard. – Rick James Oct 1 '18 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.