6 ms·

>> but the downside is that there's no standard way to parse the time from one of these IMHO that should not be a concern. The goal of UUID is to create a Uniq

>> but the downside is that there's no standard way to parse the time from one of these IMHO that should not be a concern. The goal of UUID is to create a Unique Identifier, not to record the time something was created. Maybe we should all have quantum random number generators, but with that I don't think 128 bits is enough for a UUID. Might be enough for specific applications though. First rule of database design: Your unique IDs should not have a real-world meaning.

mulmen 5y ago
> First rule of database design: Your unique IDs should not have a real-world meaning. Well, this is an opinion. I would definitely push back on making it a rule. Especially the first one. Natural keys exist, make perfectly good unique identifiers, and have inherent meaning. When using synthetic keys it is difficult (read: requires making trade-offs) to guarantee they were created in order and/or that they increase monotonically. But if you make those trade-offs (or just align expectations) you can still associate real world meaning to them. These new UUIDs standardize more of those trade-offs.
- tialaramex 5y ago
  > Natural keys exist, make perfectly good unique identifiers, and have inherent meaning. Most of the "natural" things you think are going to be good Primary Keys aren't. You quickly get into one of those "Falsehoods Programmers Believe About X" lists. I remember some years ago running into the Experian team who were designing a system to uniquely identify people and they'd got it all figured out for the US market. See, Americans have Social Security Numbers, so you can just use those to key off. Alas, as hopefully anybody here who actually deals with Social Security Numbers knows, they aren't unique. Perhaps you could argue they should be unique, but that's not useful in practice, because in practice they aren't. They seemed really disappointed about that. I doubt they fixed it though. It's far more likely that the "definitely unique" Natural key for your table isn't actually unique than that there will be an insurmountable problem caused by using artificial row IDs, UUIDs or something like that.
  - deleted 5y ago
    [deleted]
  - rkagerer 5y ago
    SSN is a terrible key (and given privacy and security concerns should probably be encrypted or otherwise peotected in any case, not showing up in logs, developers notes while troubleshooting, etc). Product serial number might be a better example. Or file hash.
    - tialaramex 5y ago
      > Product serial number might be a better example. Nope. The "serial number" is just whatever you printed on the box or maybe on the product itself. You may intend that to be unique but you've got no way to ensure that. If you tell the people in manufacturing that if any duplicates occur it's a show stopper they will tell you to stop being stupid and design your IT systems to cope with the real world. I buy serial-numbered LED lamps, the manufacturer replaces them if they drop dead within a certain number of years and I have some applications (18 hours per day of lighting) where that's plausible, so I need to track those serial numbers. Except they once shipped me a lamp (one in a box purchased together with others) with no serial number. Completely blank, no manufacturer, no type identifier, no voltage, the entire label was just missing from the product as shipped. Emailed them. No problem, apologies for the defect, your warranty registration for a lamp with no serial number will be fine.
      - staticassertion 5y ago
        I mean, they said "might be", and they're entirely correct about SSN. > Emailed them. No problem, apologies for the defect, your warranty registration for a lamp with no serial number will be fine. Seems fine?
      - michaelt 5y ago
        > If you tell the people in manufacturing that if any duplicates occur it's a show stopper they will tell you to stop being stupid and design your IT systems to cope with the real world. You've got a pretty low opinion of people in manufacturing there, buddy. No manufacturer I've ever worked with would even blink at a requirement for unique serial numbers.
- PostThisTooFast 5y ago
  But these standards assume that the proximity of records' creation time is somehow useful for sorting them. Why? If I'm doing data entry of magazine-subscription cards (because apparently it's 1985), Mr. Warren Zelkirk might have sent his card in before Anne Ackerman. How does sorting by record-creation time help me here? Keys are useful for distinguishing records unambiguously. You typically SORT records by other columns.
- williamdclt 5y ago
  > Natural keys exist They probably do, but everytime I thought something was good as a natural key, I've been wrong
  - phkahler 5y ago
    Agreed. The simplest example is names. People don't have unique names so you combine it with some other stuff (birthdate, SSN) and call it a primary key. Then someone gets married and changes their name. To anyone doing this for a short amount of time this example is obvious. The point is that almost everything you might think of as a "natural key" is not good enough and you'll get burned when you find out what the reasons are.
    - deleted 5y ago
      [deleted]
koolba 5y ago
In theory, theory and practice are the same. In practice they’re not. Sortable unique IDs have many performance benefits in real world systems.
- PostThisTooFast 5y ago
  Like what? If the sort order has any significance, it's only ONE significance, is it not?
- lonjil 5y ago
  Just put an index on whatever nice sortable unique field you happen to have, then?
wilg 5y ago
If you don't think that's a concern then UUID8 is for you!
iratewizard 5y ago
> First rule of database design: Your unique IDs should not have a real-world meaning. I see a lot of comments conflating a unique ID with a unique column. It's OK for you to have a unique contraint on something like an email column. But you wouldn't use the email column as the primary key.
remram 5y ago
If that's how you feel about it, why use UUID at all? You could use any 16 bytes and not format them according to any standard.
ryanisnan 5y ago
> to record the time something was created. I don't think that was the downside mentioned, I think it was because of the lack of standardization on how to parse the timestamp.
voidnullnil 5y ago
To take this one step further: Adding surprise semantics like capturing time (and the dev not being aware of it) will lead to security vulnerabilities (side channels because the code is essentially timestamping when various parts of the code are hit) as well as privacy issues (PII leaking, clock skew leaking, whatever). My thesis is that computers are so full of unwanted unneeded things like this that there is no engineer who knows them all as well as having a good understanding software engineering as well as infosec. Most vulnerabilities are due to lack of understanding how the primitives work, as opposed to flaws in reasoning (the former is clearly distinct: the summary provided to save him from spending a month looking at the implementation is inadequate). I have designed UUIDs myself and they are simply a long random bitstring. The random part is already necessary because we need sufficient randomness for crypto to work in the first place. IETF likes to "engineer" things.

mulmen 5y ago

> First rule of database design: Your unique IDs should not have a real-world meaning. Well, this is an opinion. I would definitely push back on making it a rule. Especially the first one. Natural keys exist, make perfectly good unique identifiers, and have inherent meaning. When using synthetic keys it is difficult (read: requires making trade-offs) to guarantee they were created in order and/or that they increase monotonically. But if you make those trade-offs (or just align expectations) you can still associate real world meaning to them. These new UUIDs standardize more of those trade-offs.

tialaramex 5y ago
> Natural keys exist, make perfectly good unique identifiers, and have inherent meaning. Most of the "natural" things you think are going to be good Primary Keys aren't. You quickly get into one of those "Falsehoods Programmers Believe About X" lists. I remember some years ago running into the Experian team who were designing a system to uniquely identify people and they'd got it all figured out for the US market. See, Americans have Social Security Numbers, so you can just use those to key off. Alas, as hopefully anybody here who actually deals with Social Security Numbers knows, they aren't unique. Perhaps you could argue they should be unique, but that's not useful in practice, because in practice they aren't. They seemed really disappointed about that. I doubt they fixed it though. It's far more likely that the "definitely unique" Natural key for your table isn't actually unique than that there will be an insurmountable problem caused by using artificial row IDs, UUIDs or something like that.
- deleted 5y ago
  [deleted]
- rkagerer 5y ago
  SSN is a terrible key (and given privacy and security concerns should probably be encrypted or otherwise peotected in any case, not showing up in logs, developers notes while troubleshooting, etc). Product serial number might be a better example. Or file hash.
  - tialaramex 5y ago
    > Product serial number might be a better example. Nope. The "serial number" is just whatever you printed on the box or maybe on the product itself. You may intend that to be unique but you've got no way to ensure that. If you tell the people in manufacturing that if any duplicates occur it's a show stopper they will tell you to stop being stupid and design your IT systems to cope with the real world. I buy serial-numbered LED lamps, the manufacturer replaces them if they drop dead within a certain number of years and I have some applications (18 hours per day of lighting) where that's plausible, so I need to track those serial numbers. Except they once shipped me a lamp (one in a box purchased together with others) with no serial number. Completely blank, no manufacturer, no type identifier, no voltage, the entire label was just missing from the product as shipped. Emailed them. No problem, apologies for the defect, your warranty registration for a lamp with no serial number will be fine.
    - staticassertion 5y ago
      I mean, they said "might be", and they're entirely correct about SSN. > Emailed them. No problem, apologies for the defect, your warranty registration for a lamp with no serial number will be fine. Seems fine?
    - michaelt 5y ago
      > If you tell the people in manufacturing that if any duplicates occur it's a show stopper they will tell you to stop being stupid and design your IT systems to cope with the real world. You've got a pretty low opinion of people in manufacturing there, buddy. No manufacturer I've ever worked with would even blink at a requirement for unique serial numbers.
PostThisTooFast 5y ago
But these standards assume that the proximity of records' creation time is somehow useful for sorting them. Why? If I'm doing data entry of magazine-subscription cards (because apparently it's 1985), Mr. Warren Zelkirk might have sent his card in before Anne Ackerman. How does sorting by record-creation time help me here? Keys are useful for distinguishing records unambiguously. You typically SORT records by other columns.
williamdclt 5y ago
> Natural keys exist They probably do, but everytime I thought something was good as a natural key, I've been wrong
- phkahler 5y ago
  Agreed. The simplest example is names. People don't have unique names so you combine it with some other stuff (birthdate, SSN) and call it a primary key. Then someone gets married and changes their name. To anyone doing this for a short amount of time this example is obvious. The point is that almost everything you might think of as a "natural key" is not good enough and you'll get burned when you find out what the reasons are.
  - deleted 5y ago
    [deleted]

koolba 5y ago

In theory, theory and practice are the same. In practice they’re not. Sortable unique IDs have many performance benefits in real world systems.

PostThisTooFast 5y ago
Like what? If the sort order has any significance, it's only ONE significance, is it not?
lonjil 5y ago
Just put an index on whatever nice sortable unique field you happen to have, then?

wilg 5y ago

If you don't think that's a concern then UUID8 is for you!

iratewizard 5y ago

> First rule of database design: Your unique IDs should not have a real-world meaning. I see a lot of comments conflating a unique ID with a unique column. It's OK for you to have a unique contraint on something like an email column. But you wouldn't use the email column as the primary key.

remram 5y ago

If that's how you feel about it, why use UUID at all? You could use any 16 bytes and not format them according to any standard.

ryanisnan 5y ago

> to record the time something was created. I don't think that was the downside mentioned, I think it was because of the lack of standardization on how to parse the timestamp.

voidnullnil 5y ago

To take this one step further: Adding surprise semantics like capturing time (and the dev not being aware of it) will lead to security vulnerabilities (side channels because the code is essentially timestamping when various parts of the code are hit) as well as privacy issues (PII leaking, clock skew leaking, whatever). My thesis is that computers are so full of unwanted unneeded things like this that there is no engineer who knows them all as well as having a good understanding software engineering as well as infosec. Most vulnerabilities are due to lack of understanding how the primitives work, as opposed to flaws in reasoning (the former is clearly distinct: the summary provided to save him from spending a month looking at the implementation is inadequate). I have designed UUIDs myself and they are simply a long random bitstring. The random part is already necessary because we need sufficient randomness for crypto to work in the first place. IETF likes to "engineer" things.