4 ms·

> SSN ... which are definitely not surrogate keys. Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incr

> SSN ... which are definitely not surrogate keys. Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).

shkkmo 2y ago
SSN is absolutely not a surrogate key. If you received a piece of information from an external source, it is data, not a surrogate key. If you use data as a key, then that is a natural key, if you invent a value to use as an identifier, that is an artificial or surrogate key. If an API provides you an ID for a record, that is data. If you use it as a key, that is also a natural key in your system.
- randomdata 2y ago
  > If you received a piece of information from an external source, it is data, not a surrogate key. It may not be your surrogate key, but it is someone's!
  - shkkmo 2y ago
    Possibly, you don't actually know since it is external data.
    - randomdata 2y ago
      We do know because the database owner has openly talked about how the keys are derived. That still doesn't make it a good key for your database, but I can assure you that the world doesn't revolve around you. It is someone's surrogate key – therefore it is a surrogate key.
      - shkkmo 2y ago
        You seem to be insisting on a semantic interpretation that makes the term less useful. A third party's surrogate key should not be considered a surrogate key when in your system. It doesn't matter how that key was generated, only that the semantic meaning of the key is outside of your control.
hathawsh 2y ago
Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. Furthermore, any time you reveal the primary key, that key can become the thing that people come to depend upon to find that database row, which leads to the possibility that someday, someone will have an important need for some primary keys to change, even if the primary key was supposed to be a surrogate key. The longer a database lives, the more likely that surrogate keys morph into natural keys. That's what happened to SSNs.
- randomdata 2y ago
  > Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.
  - hathawsh 2y ago
    SSN = social security number. What else does SSN stand for?

shkkmo 2y ago

SSN is absolutely not a surrogate key. If you received a piece of information from an external source, it is data, not a surrogate key. If you use data as a key, then that is a natural key, if you invent a value to use as an identifier, that is an artificial or surrogate key. If an API provides you an ID for a record, that is data. If you use it as a key, that is also a natural key in your system.

randomdata 2y ago
> If you received a piece of information from an external source, it is data, not a surrogate key. It may not be your surrogate key, but it is someone's!
- shkkmo 2y ago
  Possibly, you don't actually know since it is external data.
  - randomdata 2y ago
    We do know because the database owner has openly talked about how the keys are derived. That still doesn't make it a good key for your database, but I can assure you that the world doesn't revolve around you. It is someone's surrogate key – therefore it is a surrogate key.
    - shkkmo 2y ago
      You seem to be insisting on a semantic interpretation that makes the term less useful. A third party's surrogate key should not be considered a surrogate key when in your system. It doesn't matter how that key was generated, only that the semantic meaning of the key is outside of your control.

hathawsh 2y ago

Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. Furthermore, any time you reveal the primary key, that key can become the thing that people come to depend upon to find that database row, which leads to the possibility that someday, someone will have an important need for some primary keys to change, even if the primary key was supposed to be a surrogate key. The longer a database lives, the more likely that surrogate keys morph into natural keys. That's what happened to SSNs.

randomdata 2y ago
> Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.
- hathawsh 2y ago
  SSN = social security number. What else does SSN stand for?