5 ms·

The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't

The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.

hobs 2y ago
The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.
- The_Colonel 2y ago
  I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.
  - crabmusket 2y ago
    I love it when my competitors use sequential integer IDs.
  - throwaway8486k 2y ago
    Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German tank problem) Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know. https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem
    - The_Colonel 2y ago
      I'm not saying it's a bad practice, the opposite actually. > I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key. It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.
      - throwaway8486k 2y ago
        > I'm not saying it's a bad practice, the opposite actually I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have. I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer > It's not an easy thing to add into an existing application, though I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints. If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).
    - yellowapple 2y ago
      > I have been in a startup where competitors used our sequential keys to scrape a list of customers. If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.
  - deleted 2y ago
    [deleted]
  - inopinatus 2y ago
    UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit. Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.
    - The_Colonel 2y ago
      Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.
  - hobs 2y ago
    Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"
stouset 2y ago
Precisely. And if I have a surrogate key to identify “a car”, I get to define what makes a car unique for my purposes. Maybe that really is the VIN. Maybe today it’s the VIN, but tomorrow it’s whatever vehicle is currently being driven by its owner. Maybe it’s something else. Some day I may need to track multiple VINs for a vehicle (maybe it’s got parts from multiple VINs and I want to track that). I can still always decompose that table and have an n-to-1 relationship between cars and VINs without migrating the rest of my data model.
hackit2 2y ago
A entity can exist over more than one row in your database, but it is useful to uniquely identify each row as the lowest common denominator.
mkleczek 2y ago
> The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?
- unchar1 2y ago
  It may be useful if you have data that originates from another source or if something outside of our system references your entity. In that case you need to keep some form of an externalRef, so it's usually easier to just use an id that you can control, for referencing both internally and externally.
- magicalhippo 2y ago
  In the DB we use[1] the internal row id is not stable: The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table. So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead. [1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/rowid-function.html https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...
- simonw 2y ago
  Be careful with those. SQLite has a rowid concept, but it's not guaranteed to be stable - running a VACUUM against a table can reassign the rowids for every row! https://sqlite.org/rowidtable.html https://sqlite.org/rowidtable.html says: > If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
- sgarland 2y ago
  Because every DB can and will shift those as needed. They reference the physical location on disk for a given tuple. They are not meant for general consumption.

hobs 2y ago

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.

The_Colonel 2y ago
I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.
- crabmusket 2y ago
  I love it when my competitors use sequential integer IDs.
- throwaway8486k 2y ago
  Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German tank problem) Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know. https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem
  - The_Colonel 2y ago
    I'm not saying it's a bad practice, the opposite actually. > I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key. It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.
    - throwaway8486k 2y ago
      > I'm not saying it's a bad practice, the opposite actually I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have. I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer > It's not an easy thing to add into an existing application, though I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints. If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).
  - yellowapple 2y ago
    > I have been in a startup where competitors used our sequential keys to scrape a list of customers. If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.
- deleted 2y ago
  [deleted]
- inopinatus 2y ago
  UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit. Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.
  - The_Colonel 2y ago
    Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.
- hobs 2y ago
  Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"

stouset 2y ago

Precisely. And if I have a surrogate key to identify “a car”, I get to define what makes a car unique for my purposes. Maybe that really is the VIN. Maybe today it’s the VIN, but tomorrow it’s whatever vehicle is currently being driven by its owner. Maybe it’s something else. Some day I may need to track multiple VINs for a vehicle (maybe it’s got parts from multiple VINs and I want to track that). I can still always decompose that table and have an n-to-1 relationship between cars and VINs without migrating the rest of my data model.

hackit2 2y ago

A entity can exist over more than one row in your database, but it is useful to uniquely identify each row as the lowest common denominator.

mkleczek 2y ago

> The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?

unchar1 2y ago
It may be useful if you have data that originates from another source or if something outside of our system references your entity. In that case you need to keep some form of an externalRef, so it's usually easier to just use an id that you can control, for referencing both internally and externally.
magicalhippo 2y ago
In the DB we use[1] the internal row id is not stable: The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table. So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead. [1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/rowid-function.html https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...
simonw 2y ago
Be careful with those. SQLite has a rowid concept, but it's not guaranteed to be stable - running a VACUUM against a table can reassign the rowids for every row! https://sqlite.org/rowidtable.html https://sqlite.org/rowidtable.html says: > If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
sgarland 2y ago
Because every DB can and will shift those as needed. They reference the physical location on disk for a given tuple. They are not meant for general consumption.