Handling unique IDs on client side

Question

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!

Answer 1

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.