3 ms·

The example at the top of OP was of using UUIDs though. If they are supplied by a client in a distributed/async system, I guess they count as user-supplied. Th

The example at the top of OP was of using UUIDs though. If they are supplied by a client in a distributed/async system, I guess they count as user-supplied. The root of the problem with regard to UUIDs, though, seems to be that MySQL doesn't actually provide a UUID type, so the system in question was just storing their hex-dash representation as a string. There's several things going on in the OP example that deserve more discussion than they get.

marcosdumay 5y ago
Yes, they count. It doesn't matter that they are UUIDs and you can in theory continue to make them after the client changes something. If any user sees them, they aren't fit for a primary key. I do go further. Anything the user touches is unfit as a primary key. If they want some number to identify the records (in a spreadsheet, in a customer number somewhere on their work or in a public API), that number is unfit as a primary key, it doesn't matter if you are the one creating it.
- jrochkind1 5y ago
  why? The advice to never let users see PK's (basically keep them secret?) is new to me.
  - marcosdumay 5y ago
    Basically, if users see them, they will do "human things" with them, like developing a preference ("hey, that customer is deeply annoyed that we identify them by the number 666, can you change it?"); argue about the rules ("why do customer numbers go from 1, 2, 3 to 21? this is wrong"); correlate and replace it with other identifiers that they will always guarantee that are 1 to 1 with them, but never really are; and a lot of other stuff you or me will never expect. All of the columns in a table are fit for human manipulation, the only exception is the primary key that has informational rules, so use some other column if they need a key. It's not "keep them secret", it's "this data is for developers eyes only", it doesn't matter a lot where the developers are, only that they know the rules.
- dcow 5y ago
  There’s a difference between using “natural data” and using a “client generated identifier”. This thread is talking about natural data and the example given amounts to a fussy customer rather than an actual technical problem (not that they don’t exist) with using natural data. Client generated UUIDs are totally fine provided you validate the ID and authorize resource access normally e.g. client A can’t put a resource by UUID owned by client B just by knowing the UUID. Using natural data or not really seems to be a product problem and in the face of inexperienced product managers or changing requirements probably best avoided for the reasons given. It’s not clear which issue the article is addressing, specifically, though.
  - bob1029 5y ago
    > the example given amounts to a fussy customer Just so we are clear, the customer pays our salaries. Client-issued UUIDs seem fine in theory but then start to impose haunting dread when you consider the total space of all possible client devices, operating systems and browsers. At least some of those scenarios are going to have shitty rng implementation or use the wrong type of UUID. At least one of those problematic scenarios will have multiple concurrent users. What do you reach for when your client-controlled supposedly un-collideable identities begin colliding? Why even take the chance?
    - dcow 5y ago
      HTTP 400 and 409

marcosdumay 5y ago

Yes, they count. It doesn't matter that they are UUIDs and you can in theory continue to make them after the client changes something. If any user sees them, they aren't fit for a primary key. I do go further. Anything the user touches is unfit as a primary key. If they want some number to identify the records (in a spreadsheet, in a customer number somewhere on their work or in a public API), that number is unfit as a primary key, it doesn't matter if you are the one creating it.

jrochkind1 5y ago
why? The advice to never let users see PK's (basically keep them secret?) is new to me.
- marcosdumay 5y ago
  Basically, if users see them, they will do "human things" with them, like developing a preference ("hey, that customer is deeply annoyed that we identify them by the number 666, can you change it?"); argue about the rules ("why do customer numbers go from 1, 2, 3 to 21? this is wrong"); correlate and replace it with other identifiers that they will always guarantee that are 1 to 1 with them, but never really are; and a lot of other stuff you or me will never expect. All of the columns in a table are fit for human manipulation, the only exception is the primary key that has informational rules, so use some other column if they need a key. It's not "keep them secret", it's "this data is for developers eyes only", it doesn't matter a lot where the developers are, only that they know the rules.
dcow 5y ago
There’s a difference between using “natural data” and using a “client generated identifier”. This thread is talking about natural data and the example given amounts to a fussy customer rather than an actual technical problem (not that they don’t exist) with using natural data. Client generated UUIDs are totally fine provided you validate the ID and authorize resource access normally e.g. client A can’t put a resource by UUID owned by client B just by knowing the UUID. Using natural data or not really seems to be a product problem and in the face of inexperienced product managers or changing requirements probably best avoided for the reasons given. It’s not clear which issue the article is addressing, specifically, though.
- bob1029 5y ago
  > the example given amounts to a fussy customer Just so we are clear, the customer pays our salaries. Client-issued UUIDs seem fine in theory but then start to impose haunting dread when you consider the total space of all possible client devices, operating systems and browsers. At least some of those scenarios are going to have shitty rng implementation or use the wrong type of UUID. At least one of those problematic scenarios will have multiple concurrent users. What do you reach for when your client-controlled supposedly un-collideable identities begin colliding? Why even take the chance?
  - dcow 5y ago
    HTTP 400 and 409