3 ms·

Another example is where you use a service that provides you with a stable id. It makes little sense to add a surrogate id and a fk on that surrogate id. It vio

Another example is where you use a service that provides you with a stable id. It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation. Data integrity/quality matters. Adding friction to prevent accidents also matters. I don't want something accidentally and trivially updating a field that's used to reference thing externally. Something about the nat key is about to change? Fine, we can write migrations. Even if it affects millions of rows, it's not a big deal. I understand people have been burnt by bad design decisions involving nat keys, but they're not some devil's key everyone here dogmatically makes them out to be. You can mess up using anything.

maweki 2y ago
> a service that provides you with a stable id I think there's the important point. Is your key actually natural or is it someone else's surrogate key anyway? Going back to the vehicle identification number: that's already a surrogate key. You just did not assign it yourself.
- sgarland 2y ago
  A VIN is not a surrogate key. A surrogate key must, by definition, have no semantic meaning, and not be composed of parts from multiple domains (among other requirements). A VIN encodes the following: * Country of origin * Manufacturer * Vehicle type * Vehicle model * Engine type * Transmission type * Model year * Manufacturing plant * Serial number
shkkmo 2y ago
> It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation. I would still almost always use an internal artificial key on top of the external id. If you want to enforce data integrity, you can still enforce uniqueness on the external id. "Stability" of an external identifier is almost always an assumption and one that I've seen fail enough times to want that internal id by default.

maweki 2y ago

> a service that provides you with a stable id I think there's the important point. Is your key actually natural or is it someone else's surrogate key anyway? Going back to the vehicle identification number: that's already a surrogate key. You just did not assign it yourself.

sgarland 2y ago
A VIN is not a surrogate key. A surrogate key must, by definition, have no semantic meaning, and not be composed of parts from multiple domains (among other requirements). A VIN encodes the following: * Country of origin * Manufacturer * Vehicle type * Vehicle model * Engine type * Transmission type * Model year * Manufacturing plant * Serial number

shkkmo 2y ago

> It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation. I would still almost always use an internal artificial key on top of the external id. If you want to enforce data integrity, you can still enforce uniqueness on the external id. "Stability" of an external identifier is almost always an assumption and one that I've seen fail enough times to want that internal id by default.