6 ms·

> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values. I fair

> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values. I fairly strongly disagree with this. Database identifiers have to serve a lot of purposes, and natural key almost certainly isn’t ideal. Off the top my head, IDs can be used for: - Joins, lookups, indexes. Here data type can matter regarding performance and resource use. - Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs) - Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID). There is not one solution that handles all of these well. But using natural keys is one of the least good options. Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them. So ideally I’d put a unique index on the national ID column. But realistically, it would be no unique constraint and instead form validation + a warning on anytime someone opened a screen for a user with a non-unique ID. Then maybe a BIGINT for database ID, and a UUID4/7 for exposing to the world. EDIT: Actually, the article is proposing a new principle. And so perhaps this could indeed be a viable one. And my comment above would describe situations where it is valid to break the principle. But I also suspect that this is so rarely a good idea that it shouldn’t be the default choice.

Jarwain 1y ago
Why have both a database ID and UUIDv7, versus just a UUIDv7?
- sroussey 1y ago
  There is a security principle to not expose real identifiers to the outside world. It makes a crack in your system easier to open.
  - Jarwain 1y ago
    Idk that reeks of security through obscurity to me. Your authorization/permission scheme has got to be fubar'd if you're relying on obscuring IDs to prevent someone from accessing a resource they shouldn't. I'm sure I'm missing something obvious, but I'm not sure what other threat vectors there are, assuming it's in conjunction with other layers of security like a solid authorization/access control scheme. I guess I'm not the biggest fan for a few reasons. I'd rather try and design a system such that it's secure even in the case of database leak/compromise or some other form of high transparency. I don't want to encourage a culture of getting used to obscurity and potentially depending on it instead of making sure that transparency can't be abused. Also, it just feels wasteful. If you have two distinct IDs for a given resource, what are you building your foreign keys against? If you build it against the hidden one, and want to query the foreign table based on user input, you've gotta either do a join or do a query to Get the hidden key just to do another query. It just feels wasteful. EDIT: apparently RFC 4122 even says not to assume UUIDs are hard to guess and shouldn't be used for security capabilities. So if it shouldn't be depended on for security, why add all this complexity to keep it secure?
    - jandrewrogers 1y ago
      The point you may be missing is that the key itself contains information about records in the database that you don’t have access to. There are many famous examples in literature (e.g. the German Tank Problem [0]) of useful attacks on known unique serials to infer hidden information without access. In a database context, the keys for the records you are authorized to see can tell you much about the parts of the database to which you have no access. Strong randomization of record serials mitigates these attacks. [0] https://en.wikipedia.org/wiki/German_tank_problem https://en.wikipedia.org/wiki/German_tank_problem
      - Jarwain 1y ago
        I thought we were talking about UUIDv7, which is random enough to make this not a problem right?
- jandrewrogers 1y ago
  Actually, it should be a database ID and an encrypted database ID, which doesn’t require storing a second ID. Even better, you can make that key unique per session so that users can’t share keys. For security reasons, it is a bad idea to leak private state, which UUIDv7 does. A single AES encryption block is the same size as a UUID and cheap to compute.
  - adamcharnock 1y ago
    > A single AES encryption block is the same size as a UUID and cheap to compute. I didn’t realise this! The UUID spec mandates some values for specific digits, so I assume this would not be strictly valid UUIDs?
    - jandrewrogers 1y ago
      They would not be valid UUIDs, it is an opaque 128-bit value. To be honest many companies are not using strictly standard conforming UUIDs everywhere, and UUID has become a synonym for opaque 128-bit identifier. Consumers of the value just need them to be unique. All standard UUID versions are sometimes prohibited in environments, except maybe UUIDv8, so this semantic ambiguity is helpful. Technically, you could make it present as a standard UUIDv4 or UUIDv8 by setting a few bits, as long as you remember to add them back if you ever need to decrypt it. The entropy might be a bit off for a UUIDv4 if someone actually checks but you can guarantee the uniqueness. Using AES to produce a UUID-like key is an old but useful trick. Both ARM and x86 do that encryption in hardware — it is cheaper to generate than the standardized UUID versions in most cases.
      - sysysy 1y ago
        Which mode of AES do you mean? AES ECB seems to fit your example, but wouldn't it still expose the incremental nature UUID v7 with enough samples (see https://en.wikipedia.org/wiki/Block_cipher_mode_of_operation https://en.wikipedia.org/wiki/Block_cipher_mode_of_operation, the Linux Tux image example)? Other AES modes produce way longer results, even when the ID is encoded with Base58 or the like.
    - deleted 1y ago
jiggawatts 1y ago
> will absolutely swear that there will never be two people with the same national ID... I suddenly got flash-backs. There are duplicate ISBN numbers for books, despite the system being carefully designed to avoid this. There are ISBN numbers that have invalid checksums, but are valid ISBNs with the invalid number in the barcode and everything. Either the calculation was incorrectly done, or it was simply a mis-print. The same book can have hundreds of ISBNs. There is no sane way to determine if two such ISBNs are truly the same (page numbers and everything), or a reprint that has renumbered pages or even subtly different content with corrected typos, missing or added illustrations, etc... Our federal government publishes a master database of "job id" numbers for each profession one could have. This is critical for legislation related to skilled migrants, collective workplace agreements, etc... The states decided to add one digit to these numbers to further subdivide them. They did it differently, of course, and some didn't subdivide at all. Some of them have typos with "O" in place of "0" in a few places. Some states dropped the leading zeroes, and then added a suffix digit, which is fun. On and on and on... The real world is messy. Any ID you don't generate yourself is fraught with risk. Even then there are issues such as what happens if the database is rolled back to a backup and then IDs are generated again for the missed data!
- lscharen 1y ago
  The states decided to add one digit to these numbers to further subdivide them. They did it differently, of course, and some didn't subdivide at all. Some of them have typos with "O" in place of "0" in a few places. Some states dropped the leading zeroes, and then added a suffix digit, which is fun. Any identifier that is comprised of digits but is not a number will have a hilariously large amount of mistakes and alterations like you describe. In my own work I see this all the time with FIPS codes and parcel identifiers -- mostly because someone has round-tripped their data through Excel which will autocast the identifiers to numeric types. Federal GEOIDs are particularly tough because the number of digits defines the GEOID type and there are valid types for 10, 11 and 12-digit numbers, so dropping a leading zero wreaks havoc on any automated processing. There's a lot of ways to create the garbage in GIGO.
bruce511 1y ago
I'm with you. I've used natural keys in the past, and they've always been a problem eventually. On the other hand I've used surrogate keys for 20 years, and never encountered an issue that wasn't simple to resolve. I get there are different camps here, and yes your context matters, but "I'm not really interested in why natural keys worked for you." They don't work for me. So arguments for natural keys are kinda meh. I guess they work for some folk (shrug).
jandrewrogers 1y ago
> Allowing a client to generate IDs can be a big help here (ie UUIDs) Trusting the client to generate a high-quality ID has a long history of being a bad idea in practice. It requires the client to not be misconfigured, to not be hacked, to not be malicious, to not have hardware bugs, etc. A single server can generate hundreds of millions of IDs per second and provides a single point of monitoring and control.
- treyd 1y ago
  In context I read that as database client, meaning the application server (which is a client to the database) providing the service to the user. Having that be able to generate IDs could be useful when needing to refer to the same entity, even if there is data that has to exist in some separate database for some reason.
  - adamcharnock 1y ago
    That is indeed what I had in mind, although I did leave it intentionally vague as everyone can asses what’s best for their own situation
- qazxcvbnm 1y ago
  I’ve once attempted to implement a solution where ids are generated by UUIDv5 from a certain owner and the relationship of the new item to the owner; that way, users cannot generate arbitrary ids but can still predict ahead of time their new ids to ease optimistic behaviour.
- deleted 1y ago
  [deleted]
- adrianmsmith 1y ago
  I think if you have (tenant, uuid) or (user, uuid) or whatever as your primary key, that's fine. If that tenant or user or whatever generates rubbish keys, that's their problem.
sitharus 1y ago
This isn’t a new principle, it was part of database design courses in the early 2000s at least. However from a couple of decades of bitter experience I say external keys should never be your primary keys. They’ll always change for some reason. Yes you can create your tables with ON UPDATE CASCADE foreign keys, but are those really the only places the ID is used? Sometimes your own data does present a natural key though, so if it’s fully within your control then it’s a decent idea to use.
- whstl 1y ago
  Advice used to be that "natural keys can only be primary if they don't change" but there was always an exception to the rule after a few months. On the other hand, I remember a CEO wanting to be #1 in the database, so even non-natural primary keys can change... haha.
- prmph 1y ago
  Use both. For each table, I use an internal id that is auto generated, and an external uuid.
- bunderbunder 1y ago
  Even internal keys. For example, suppose you have an information management system where the user can define their own logical fields or attributes. Naturally those names should uniquely identify a field. That makes them an easy candidate for a natural key. But I would still use a surrogate key. I've worked in two systems that had this feature. In one the field name was a primary key, and in the other they used a surrogate key and a separate uniqueness constraint. In both a requirement to let users rename fields was added later. In the one that used a surrogate key, this was an easy and straightforward change. In the one that used field name as a natural key, the team decided that implementing the feature wasn't feasible. The name change operation, with its cascade of values needing to be updated for all the foreign key relationships, would have been way too big a transaction to be doing on the fly, in a live database, during high usage times of day. So instead they added a separate "display name" field and updated all the queries to coalesce that with the "official" name. Which is just ugly, and also horribly confusing. Especially in oddball cases where users did something like swapping the names of two fields.
Akronymus 1y ago
As someone who currently has to deal with a database that has NO surrogate keys, 100% agree with having surrogate keys always. Compound natural keys are just plain awful to deal with. Altough, it doesn't help that we have to do string manipulation to extract part of those natural keys from other fields that contain a bunch of data as one long string.
friendzis 1y ago
> Joins, lookups, indexes. You both want to control these values within your database engine, at least so that they are actually unique within the domain, and there is no real reason for it to be user-controlled anyway, as they are used referentially. > Idempotency. User supplied tokens for idempotency are mostly useful within the broader context of application sitting on top of database, otherwise they become subject to the same requirements internally generated ones are, without control, which is a recipe for disaster. > Sharing Those are the same idempotency tokens from previous points with you as the supplier. In some cases you want to share them across prod/stage/dev environments, in some cases you may want to explicitly avoid duplicates, in some cases you don't care. All these use cases are solved with mapping tables / classifiers. Example: in an asset management system you need to expose and identifier of a user-registered computer, that is built using components procured from different suppliers, with their own identification schemas, e.g. exposing keyboard/mouse combo as one component with two subcomponents or two (possibly linked) components. This requires you to use all those listed identifier types in different parts of the system. You can bake those in database/schema design, or employ some normalization and use "native" identifier and mapping tables.
b-man 1y ago
- Joins, lookups, indexes. Here data type can matter regarding performance and resource use. I struggle to see a practical example. > - Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs) Natural keys solves this > - Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID). The you have another piece of data, which you relate to the natural key. Something like `exposed-name`. > There is not one solution that handles all of these well Natural keys solve these issues. > Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them. If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time. > Actually, the article is proposing a new principle I'm putting it in words, but such knowledge has been common in the database community for ages, afaict.
- jasonlotito 1y ago
  > Natural keys solves this It would be helpful if the article used a natural key. Instead, it uses a primary key that is neither natural nor guaranteed, and is mutable. It makes assumptions that are not true, and that is one of the big dangers in using natural keys. > If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time. This again is a dangerous assumption. The danger here is the assumption that facts don't change. Facts do change. And facts that are true at design time are not necessarily true 1 day later, 1 year later, or 1 decade later, or more. Again, when the example can't even use a natural key to present it's idea of using natural keys, we have a problem.
- hobs 1y ago
  > I struggle to see a practical example. Memory, and CPU, and even storage eventually, those would be the main practical examples of where having a key that's composed of something very small saves you space and thus, time. Say we want to use a bigint key vs a VARCHAR(30)? depending on your big key you might be talking about terabytes of additional data, just to store a key (1t rows @ bigint = 8TB, 1T rows at 30 chars? 30TB...). The data also is going to constantly shuffle (random inserts). If you want to define the PK as the natural key with no separate column then you get to do comparisons on all the natural key columns themselves, so instead of doing 1 4 or 8 byte column comparison you get to do what? 5 char comparisons? Having worked extensively in ETL - when a developer tells me "there's no duplication about this real world process" what they mean is "there's no duplication in my mental model about this real world process"
DrScientist 1y ago
I agree - in many cases a surrogate key is better than a natural key. However there is a problem when people start creating surrogate keys by default and stop thinking about the natural candidate key at all, nevermind not putting on the appropriate constraints. Especially in the case where the natural key values are within your control. Also not using them for the key ( and FK ) can create the need for additional joins just to go from surrogate to natural key value.
karmakaze 1y ago
The writing style was already obtuse and off-putting. Adding this 'new' principle (claimed as an innovation), leads me to believe it's not worth my time to read. Additionally the principles are great starting points but real-world uses may require deviation and any thought that absolutes should prevail indicates a lack of experience.
marcnavarre 1y ago
- Author makes an ontological statement - Somehow someone feels its appropriate to talk about mechanisms
mamcx 1y ago
We can debate the first usage (small type for storage/cpu optimization) but the other 2 are actually good examples of natural keys! Note the quotes: > Every software project needs to represent the reality of the business he is embedded in... > Such database needs to be designed to properly reflect reality. This can’t be automated, since the semantics of the situation need to be encoded in a way that can be processed by a computer. Having the business need to share an ID, not give internal information, make some data opaque, use a 'arbitary' id to share as 'textual pointer' to the actual data, etc are valid natural keys. What is wrong is just add a surrogate key just because, when the fact is that a natural key (to the domain, the business, the requirements) is the actual answer. I discover this fact when doing sync. The moment I get rid of UUIDs and similar and instead keep data-as-natural things become MORE clear: ulid: 01H6GZ8YQJ code: A1 name: A ulid: 01H6GZ8YQK code: A1 name: A ulid: 01H6GZ8YQJL code: A1 name: A // All same, obscured by wrong key vs code: A1 name: A (ie: The actual data and what I need to model a sync is NOT the same. Is similar to how in 'git' your code is one thing that is clearly distinct to the internal git structures that encode the repo)
- HelloNurse 1y ago
  I'd expect both code and name to be subject to deliberate changes (e.g. name "A" is extended to "Alpha" after the name column is extended from 1 to 5 characters) or "technical" edits (e.g. due to out of order data entry, code "A1" should become "A2" in order to insert the proper A1 record). Anything the user sees is a commitment.
  - mamcx 1y ago
    Sure. Data is not static. But is foolish to pretend is. Adding surrogate keys not changed that, only add other column that COULD change.

Jarwain 1y ago

Why have both a database ID and UUIDv7, versus just a UUIDv7?

sroussey 1y ago
There is a security principle to not expose real identifiers to the outside world. It makes a crack in your system easier to open.
- Jarwain 1y ago
  Idk that reeks of security through obscurity to me. Your authorization/permission scheme has got to be fubar'd if you're relying on obscuring IDs to prevent someone from accessing a resource they shouldn't. I'm sure I'm missing something obvious, but I'm not sure what other threat vectors there are, assuming it's in conjunction with other layers of security like a solid authorization/access control scheme. I guess I'm not the biggest fan for a few reasons. I'd rather try and design a system such that it's secure even in the case of database leak/compromise or some other form of high transparency. I don't want to encourage a culture of getting used to obscurity and potentially depending on it instead of making sure that transparency can't be abused. Also, it just feels wasteful. If you have two distinct IDs for a given resource, what are you building your foreign keys against? If you build it against the hidden one, and want to query the foreign table based on user input, you've gotta either do a join or do a query to Get the hidden key just to do another query. It just feels wasteful. EDIT: apparently RFC 4122 even says not to assume UUIDs are hard to guess and shouldn't be used for security capabilities. So if it shouldn't be depended on for security, why add all this complexity to keep it secure?
  - jandrewrogers 1y ago
    The point you may be missing is that the key itself contains information about records in the database that you don’t have access to. There are many famous examples in literature (e.g. the German Tank Problem [0]) of useful attacks on known unique serials to infer hidden information without access. In a database context, the keys for the records you are authorized to see can tell you much about the parts of the database to which you have no access. Strong randomization of record serials mitigates these attacks. [0] https://en.wikipedia.org/wiki/German_tank_problem https://en.wikipedia.org/wiki/German_tank_problem
    - Jarwain 1y ago
      I thought we were talking about UUIDv7, which is random enough to make this not a problem right?
jandrewrogers 1y ago
Actually, it should be a database ID and an encrypted database ID, which doesn’t require storing a second ID. Even better, you can make that key unique per session so that users can’t share keys. For security reasons, it is a bad idea to leak private state, which UUIDv7 does. A single AES encryption block is the same size as a UUID and cheap to compute.
- adamcharnock 1y ago
  > A single AES encryption block is the same size as a UUID and cheap to compute. I didn’t realise this! The UUID spec mandates some values for specific digits, so I assume this would not be strictly valid UUIDs?
  - jandrewrogers 1y ago
    They would not be valid UUIDs, it is an opaque 128-bit value. To be honest many companies are not using strictly standard conforming UUIDs everywhere, and UUID has become a synonym for opaque 128-bit identifier. Consumers of the value just need them to be unique. All standard UUID versions are sometimes prohibited in environments, except maybe UUIDv8, so this semantic ambiguity is helpful. Technically, you could make it present as a standard UUIDv4 or UUIDv8 by setting a few bits, as long as you remember to add them back if you ever need to decrypt it. The entropy might be a bit off for a UUIDv4 if someone actually checks but you can guarantee the uniqueness. Using AES to produce a UUID-like key is an old but useful trick. Both ARM and x86 do that encryption in hardware — it is cheaper to generate than the standardized UUID versions in most cases.
    - sysysy 1y ago
      Which mode of AES do you mean? AES ECB seems to fit your example, but wouldn't it still expose the incremental nature UUID v7 with enough samples (see https://en.wikipedia.org/wiki/Block_cipher_mode_of_operation https://en.wikipedia.org/wiki/Block_cipher_mode_of_operation, the Linux Tux image example)? Other AES modes produce way longer results, even when the ID is encoded with Base58 or the like.
  - deleted 1y ago

jiggawatts 1y ago

> will absolutely swear that there will never be two people with the same national ID... I suddenly got flash-backs. There are duplicate ISBN numbers for books, despite the system being carefully designed to avoid this. There are ISBN numbers that have invalid checksums, but are valid ISBNs with the invalid number in the barcode and everything. Either the calculation was incorrectly done, or it was simply a mis-print. The same book can have hundreds of ISBNs. There is no sane way to determine if two such ISBNs are truly the same (page numbers and everything), or a reprint that has renumbered pages or even subtly different content with corrected typos, missing or added illustrations, etc... Our federal government publishes a master database of "job id" numbers for each profession one could have. This is critical for legislation related to skilled migrants, collective workplace agreements, etc... The states decided to add one digit to these numbers to further subdivide them. They did it differently, of course, and some didn't subdivide at all. Some of them have typos with "O" in place of "0" in a few places. Some states dropped the leading zeroes, and then added a suffix digit, which is fun. On and on and on... The real world is messy. Any ID you don't generate yourself is fraught with risk. Even then there are issues such as what happens if the database is rolled back to a backup and then IDs are generated again for the missed data!

lscharen 1y ago
The states decided to add one digit to these numbers to further subdivide them. They did it differently, of course, and some didn't subdivide at all. Some of them have typos with "O" in place of "0" in a few places. Some states dropped the leading zeroes, and then added a suffix digit, which is fun. Any identifier that is comprised of digits but is not a number will have a hilariously large amount of mistakes and alterations like you describe. In my own work I see this all the time with FIPS codes and parcel identifiers -- mostly because someone has round-tripped their data through Excel which will autocast the identifiers to numeric types. Federal GEOIDs are particularly tough because the number of digits defines the GEOID type and there are valid types for 10, 11 and 12-digit numbers, so dropping a leading zero wreaks havoc on any automated processing. There's a lot of ways to create the garbage in GIGO.

bruce511 1y ago

I'm with you. I've used natural keys in the past, and they've always been a problem eventually. On the other hand I've used surrogate keys for 20 years, and never encountered an issue that wasn't simple to resolve. I get there are different camps here, and yes your context matters, but "I'm not really interested in why natural keys worked for you." They don't work for me. So arguments for natural keys are kinda meh. I guess they work for some folk (shrug).

jandrewrogers 1y ago

> Allowing a client to generate IDs can be a big help here (ie UUIDs) Trusting the client to generate a high-quality ID has a long history of being a bad idea in practice. It requires the client to not be misconfigured, to not be hacked, to not be malicious, to not have hardware bugs, etc. A single server can generate hundreds of millions of IDs per second and provides a single point of monitoring and control.

treyd 1y ago
In context I read that as database client, meaning the application server (which is a client to the database) providing the service to the user. Having that be able to generate IDs could be useful when needing to refer to the same entity, even if there is data that has to exist in some separate database for some reason.
- adamcharnock 1y ago
  That is indeed what I had in mind, although I did leave it intentionally vague as everyone can asses what’s best for their own situation
qazxcvbnm 1y ago
I’ve once attempted to implement a solution where ids are generated by UUIDv5 from a certain owner and the relationship of the new item to the owner; that way, users cannot generate arbitrary ids but can still predict ahead of time their new ids to ease optimistic behaviour.
deleted 1y ago
[deleted]
adrianmsmith 1y ago
I think if you have (tenant, uuid) or (user, uuid) or whatever as your primary key, that's fine. If that tenant or user or whatever generates rubbish keys, that's their problem.

sitharus 1y ago

This isn’t a new principle, it was part of database design courses in the early 2000s at least. However from a couple of decades of bitter experience I say external keys should never be your primary keys. They’ll always change for some reason. Yes you can create your tables with ON UPDATE CASCADE foreign keys, but are those really the only places the ID is used? Sometimes your own data does present a natural key though, so if it’s fully within your control then it’s a decent idea to use.

whstl 1y ago
Advice used to be that "natural keys can only be primary if they don't change" but there was always an exception to the rule after a few months. On the other hand, I remember a CEO wanting to be #1 in the database, so even non-natural primary keys can change... haha.
prmph 1y ago
Use both. For each table, I use an internal id that is auto generated, and an external uuid.
bunderbunder 1y ago
Even internal keys. For example, suppose you have an information management system where the user can define their own logical fields or attributes. Naturally those names should uniquely identify a field. That makes them an easy candidate for a natural key. But I would still use a surrogate key. I've worked in two systems that had this feature. In one the field name was a primary key, and in the other they used a surrogate key and a separate uniqueness constraint. In both a requirement to let users rename fields was added later. In the one that used a surrogate key, this was an easy and straightforward change. In the one that used field name as a natural key, the team decided that implementing the feature wasn't feasible. The name change operation, with its cascade of values needing to be updated for all the foreign key relationships, would have been way too big a transaction to be doing on the fly, in a live database, during high usage times of day. So instead they added a separate "display name" field and updated all the queries to coalesce that with the "official" name. Which is just ugly, and also horribly confusing. Especially in oddball cases where users did something like swapping the names of two fields.

Akronymus 1y ago

As someone who currently has to deal with a database that has NO surrogate keys, 100% agree with having surrogate keys always. Compound natural keys are just plain awful to deal with. Altough, it doesn't help that we have to do string manipulation to extract part of those natural keys from other fields that contain a bunch of data as one long string.

friendzis 1y ago

> Joins, lookups, indexes. You both want to control these values within your database engine, at least so that they are actually unique within the domain, and there is no real reason for it to be user-controlled anyway, as they are used referentially. > Idempotency. User supplied tokens for idempotency are mostly useful within the broader context of application sitting on top of database, otherwise they become subject to the same requirements internally generated ones are, without control, which is a recipe for disaster. > Sharing Those are the same idempotency tokens from previous points with you as the supplier. In some cases you want to share them across prod/stage/dev environments, in some cases you may want to explicitly avoid duplicates, in some cases you don't care. All these use cases are solved with mapping tables / classifiers. Example: in an asset management system you need to expose and identifier of a user-registered computer, that is built using components procured from different suppliers, with their own identification schemas, e.g. exposing keyboard/mouse combo as one component with two subcomponents or two (possibly linked) components. This requires you to use all those listed identifier types in different parts of the system. You can bake those in database/schema design, or employ some normalization and use "native" identifier and mapping tables.

b-man 1y ago

- Joins, lookups, indexes. Here data type can matter regarding performance and resource use. I struggle to see a practical example. > - Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs) Natural keys solves this > - Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID). The you have another piece of data, which you relate to the natural key. Something like `exposed-name`. > There is not one solution that handles all of these well Natural keys solve these issues. > Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them. If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time. > Actually, the article is proposing a new principle I'm putting it in words, but such knowledge has been common in the database community for ages, afaict.

jasonlotito 1y ago
> Natural keys solves this It would be helpful if the article used a natural key. Instead, it uses a primary key that is neither natural nor guaranteed, and is mutable. It makes assumptions that are not true, and that is one of the big dangers in using natural keys. > If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time. This again is a dangerous assumption. The danger here is the assumption that facts don't change. Facts do change. And facts that are true at design time are not necessarily true 1 day later, 1 year later, or 1 decade later, or more. Again, when the example can't even use a natural key to present it's idea of using natural keys, we have a problem.
hobs 1y ago
> I struggle to see a practical example. Memory, and CPU, and even storage eventually, those would be the main practical examples of where having a key that's composed of something very small saves you space and thus, time. Say we want to use a bigint key vs a VARCHAR(30)? depending on your big key you might be talking about terabytes of additional data, just to store a key (1t rows @ bigint = 8TB, 1T rows at 30 chars? 30TB...). The data also is going to constantly shuffle (random inserts). If you want to define the PK as the natural key with no separate column then you get to do comparisons on all the natural key columns themselves, so instead of doing 1 4 or 8 byte column comparison you get to do what? 5 char comparisons? Having worked extensively in ETL - when a developer tells me "there's no duplication about this real world process" what they mean is "there's no duplication in my mental model about this real world process"

DrScientist 1y ago

I agree - in many cases a surrogate key is better than a natural key. However there is a problem when people start creating surrogate keys by default and stop thinking about the natural candidate key at all, nevermind not putting on the appropriate constraints. Especially in the case where the natural key values are within your control. Also not using them for the key ( and FK ) can create the need for additional joins just to go from surrogate to natural key value.

karmakaze 1y ago

The writing style was already obtuse and off-putting. Adding this 'new' principle (claimed as an innovation), leads me to believe it's not worth my time to read. Additionally the principles are great starting points but real-world uses may require deviation and any thought that absolutes should prevail indicates a lack of experience.

marcnavarre 1y ago

- Author makes an ontological statement - Somehow someone feels its appropriate to talk about mechanisms

mamcx 1y ago

We can debate the first usage (small type for storage/cpu optimization) but the other 2 are actually good examples of natural keys! Note the quotes: > Every software project needs to represent the reality of the business he is embedded in... > Such database needs to be designed to properly reflect reality. This can’t be automated, since the semantics of the situation need to be encoded in a way that can be processed by a computer. Having the business need to share an ID, not give internal information, make some data opaque, use a 'arbitary' id to share as 'textual pointer' to the actual data, etc are valid natural keys. What is wrong is just add a surrogate key just because, when the fact is that a natural key (to the domain, the business, the requirements) is the actual answer. I discover this fact when doing sync. The moment I get rid of UUIDs and similar and instead keep data-as-natural things become MORE clear: ulid: 01H6GZ8YQJ code: A1 name: A ulid: 01H6GZ8YQK code: A1 name: A ulid: 01H6GZ8YQJL code: A1 name: A // All same, obscured by wrong key vs code: A1 name: A (ie: The actual data and what I need to model a sync is NOT the same. Is similar to how in 'git' your code is one thing that is clearly distinct to the internal git structures that encode the repo)

HelloNurse 1y ago
I'd expect both code and name to be subject to deliberate changes (e.g. name "A" is extended to "Alpha" after the name column is extended from 1 to 5 characters) or "technical" edits (e.g. due to out of order data entry, code "A1" should become "A2" in order to insert the proper A1 record). Anything the user sees is a commitment.
- mamcx 1y ago
  Sure. Data is not static. But is foolish to pretend is. Adding surrogate keys not changed that, only add other column that COULD change.