Mysql – Handling unique IDs on client side

MySQLschema

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:

Not using auto incrementing but a uuid or guid, but I've read these are not all that advisable, especially for db performance.
Concealing the client side id by hashing it when served on the server side. This also doesn't sound particularly performant.
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.
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.
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!

Best Answer

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.

Related Solutions

Sql-server – Guidance for using composite keys to identify rows

After thinking about this, I would revise your table structure just slightly.
First, revise your sheet-size table:

Sheet_size
===========
Id
Length
Width
Thickness

Second, create a sheet-size/type relationship table:

Sheet_size_type
================
Sheet_Type_Id
Sheet_Size_Id

Then, create the following constraints:

The primary key (and index) of Sheet_size should be the id column
There should be some sort of unique key (and index) enforced on the dimensions in Sheet_size. Consider, are two sheets of dimensions (48, 96, .5) and (96, 48, .5) equal (that is, does the direction of the dimensions matter)? This sort of problem may be difficult to enforce if through the use of the columns as part of the primary key, but becomes more manageable when using constraints and stored procedures.
The primary key (and index) of Sheet_size_type should use both of the foreign keys, starting with the one with the lower cardinality (probably sheet_type, given your example). You may want an additional index in the other direction, but it might not be necessary.

This revision will save you database space (as a ratio of the number of sheet types using the same size), and shouldn't impact overhead too much.

There are other potential concerns about equality/uniqueness if you are using a float datatype, as the imprecision may trip you up unexpectedly. You should consider whether or not a fixed-point type, to some given precision, would be more appropriate.

Mysql – Table attribute for sorting

Without seeing the table schema, I see two options:

Adjust the schema so that the Foreign Keys(FK) are not part of the Primary Key(PK); only the AUTO_INCREMENT column is the PK. The Foreign Keys can still be Unique Indexes to prevent multiple likes of a particular track by the same user. This won't work of course if the FKs are part of a partitioning scheme.
Take out the AUTO_INCREMENT column and add a TIMESTAMP(0) DEFAULT CURRENT_TIME column. The timestamp column will take up as much space as an INT AUTO_INCREMENT column. The downside to this is all rows before adding the timestamp column would not have an order.

Best Answer

Related Solutions

Sql-server – Guidance for using composite keys to identify rows

Mysql – Table attribute for sorting

Related Question