4 ms·

It's rare in practice but occasionally rows have natural unique identifiers that are useable. The textbook case would be student IDs in a university database w

It's rare in practice but occasionally rows have natural unique identifiers that are useable. The textbook case would be student IDs in a university database where each row corresponds to a single student. However some universities view the student id as sensitive information (kind of like a ssn in the US) and so in that scenario it should be aliased to some other key to prevent the student id from being present in multiple tables as a foreign key.

TeMPOraL 5y ago
> However some universities view the student id as sensitive information (kind of like a ssn in the US) and so in that scenario it should be aliased to some other key to prevent the student id from being present in multiple tables as a foreign key. It is ironic, given that the whole reason to have a student ID is for it to be a primary key in some database somewhere.
- whatshisface 5y ago
  Databases are vulnerable to a particular type of folkloric magic: https://en.m.wikipedia.org/wiki/True_name https://en.m.wikipedia.org/wiki/True_name
jameshart 5y ago
Student IDs are certainly not ‘natural’ keys. I am almost entirely convinced that there is actually no such thing as a natural key.
- mulmen 5y ago
  Natural keys are everywhere. They are just less interesting than the relationships. My name is a natural key. It uniquely identifies my name. The wheels come off if you expect it to uniquely identify me. But with natural keys that's never actually necessary. When I buy a car they record my name, my date of birth, the time of the sale and some details about the car, such as the VIN, which is its name. That provides enough information to form a natural key to identify the sale. At no point does a database actually have a concept of who I am really, only the relevant data points to satisfy its own relational model. Any database that means to track information about people would be able to construct a natural key from my name, my mothers name and some information about my birth, such as the doctor, hospital and time.
  - jameshart 5y ago
    (Interesting cultural assumption there that people are born in hospitals under the supervision of doctors) The thing is to qualify as a natural key I think it has to be precisely recoverable just from first principles wherever you encounter the artifact it references. The birth details aren’t inherent to the person, they’re datapoints about them. All the identifiers you propose to make up mnatural’ keys are synthetic keys from some other authority for identifying things. VINs obviously are synthetic identifiers, but so are names, dates, hospital addresses. As you rightly say, a string is a natural key for itself; likewise a number. The only other ‘natural key’ I can think of is atomic number for identifying chemical elements.
    - mulmen 5y ago
      I said a database that tracks people could identify me using information about my birth. Not that everyone is born in a hospital or by a doctor. By your definition there are no natural keys. But since that definition isn't useful we instead use one that is. The natural key is the identifier by which the thing is already known. https://en.wikipedia.org/wiki/Natural_key https://en.wikipedia.org/wiki/Natural_key
      - CRConrad 5y ago
        > I said a database that tracks people could identify me using information about my birth. And your name, you said. > The natural key is the identifier by which the thing is already known. And then you change your name.
  - denton-scratch 5y ago
    Probably not unique, though. Well, yours might be; but in general, a person's name isn't a reliable unique identifier. Nor anyone's name. Even if you have a timestramp, you can't be sure it's unique.
- CharlesW 5y ago
  > I am almost entirely convinced that there is actually no such thing as a natural key. Yes! Or if it is now, there's a future scenario where that will have proven to have been a poor decision.
- wolfgang42 5y ago
  a "natural key" is frequently just a really foreign key in a database you and your org don't manage. — 'wweston, https://news.ycombinator.com/item?id=27349246 https://news.ycombinator.com/item?id=27349246
- dragonwriter 5y ago
  Dates are natural keys in a calendar table. The composite of the foreign key values is a natural key in a join table expressing a unique relationship between entities in two or more other tables, perhaps with additional attributes, even if those values are surrogate keys in their own tables.
  - jameshart 5y ago
    Dates aren’t natural. I know of at least three different date keys in different calendars that refer to the same calendar day - choosing to use one particular date standard to refer to days is choosing a pre-existing synthetic key, not a ‘natural’ one.
    - mulmen 5y ago
      Dates are natural keys. You seem to be conflating format and value. 1,234,567 and 1234567 are the same number. 19-Jun-21 and 2021-06-19 are the same date. Databases already handle this.
      - jameshart 5y ago
        19 June 2021 is also known as: Dhuʻl-Qiʻdah 9, 1442 AH 9 Tamuz 5781
        mulmen 5y ago
        Great! Then if they are unambiguous they can be inserted directly or disambiguated and then inserted. At query time any format can be used to represent them to a user.
        jameshart 5y ago
        Ah, so you're suggesting instead of storing 'what a person calls this date' as the key, you instead convert it to some underlying representation that you can then choose to present in different calendars - like a UNIX epoch-relative offset or something. Congratulations, you just created another synthetic key for describing dates.
wombatpm 5y ago
In the 1980's your student ID was most likely your SSN
dragonwriter 5y ago
> The textbook case would be student IDs in a university database where each row corresponds to a single student “Student IDs” are an example of surrogate keys, though they may be surrogate keys in a system predating and outside of the DB. (And if the mapping between them and actual students are managed by an error-prone process outside of the DB, they probably aren’t good primary keys for a table of students.)
bart_spoon 5y ago
I think a natural key that I’ve encountered is a database containing professional NBA data. Teams never play more than once per day, and there are only two teams in the game, so a natural (composite) key for games is simply the date of the game and the two teams that played. You can create a “game ID” if you wish, but you aren’t actually doing anything but adding a column of redundant information.

TeMPOraL 5y ago

> However some universities view the student id as sensitive information (kind of like a ssn in the US) and so in that scenario it should be aliased to some other key to prevent the student id from being present in multiple tables as a foreign key. It is ironic, given that the whole reason to have a student ID is for it to be a primary key in some database somewhere.

whatshisface 5y ago
Databases are vulnerable to a particular type of folkloric magic: https://en.m.wikipedia.org/wiki/True_name https://en.m.wikipedia.org/wiki/True_name

jameshart 5y ago

Student IDs are certainly not ‘natural’ keys. I am almost entirely convinced that there is actually no such thing as a natural key.

mulmen 5y ago
Natural keys are everywhere. They are just less interesting than the relationships. My name is a natural key. It uniquely identifies my name. The wheels come off if you expect it to uniquely identify me. But with natural keys that's never actually necessary. When I buy a car they record my name, my date of birth, the time of the sale and some details about the car, such as the VIN, which is its name. That provides enough information to form a natural key to identify the sale. At no point does a database actually have a concept of who I am really, only the relevant data points to satisfy its own relational model. Any database that means to track information about people would be able to construct a natural key from my name, my mothers name and some information about my birth, such as the doctor, hospital and time.
- jameshart 5y ago
  (Interesting cultural assumption there that people are born in hospitals under the supervision of doctors) The thing is to qualify as a natural key I think it has to be precisely recoverable just from first principles wherever you encounter the artifact it references. The birth details aren’t inherent to the person, they’re datapoints about them. All the identifiers you propose to make up mnatural’ keys are synthetic keys from some other authority for identifying things. VINs obviously are synthetic identifiers, but so are names, dates, hospital addresses. As you rightly say, a string is a natural key for itself; likewise a number. The only other ‘natural key’ I can think of is atomic number for identifying chemical elements.
  - mulmen 5y ago
    I said a database that tracks people could identify me using information about my birth. Not that everyone is born in a hospital or by a doctor. By your definition there are no natural keys. But since that definition isn't useful we instead use one that is. The natural key is the identifier by which the thing is already known. https://en.wikipedia.org/wiki/Natural_key https://en.wikipedia.org/wiki/Natural_key
    - CRConrad 5y ago
      > I said a database that tracks people could identify me using information about my birth. And your name, you said. > The natural key is the identifier by which the thing is already known. And then you change your name.
- denton-scratch 5y ago
  Probably not unique, though. Well, yours might be; but in general, a person's name isn't a reliable unique identifier. Nor anyone's name. Even if you have a timestramp, you can't be sure it's unique.
CharlesW 5y ago
> I am almost entirely convinced that there is actually no such thing as a natural key. Yes! Or if it is now, there's a future scenario where that will have proven to have been a poor decision.
wolfgang42 5y ago
a "natural key" is frequently just a really foreign key in a database you and your org don't manage. — 'wweston, https://news.ycombinator.com/item?id=27349246 https://news.ycombinator.com/item?id=27349246
dragonwriter 5y ago
Dates are natural keys in a calendar table. The composite of the foreign key values is a natural key in a join table expressing a unique relationship between entities in two or more other tables, perhaps with additional attributes, even if those values are surrogate keys in their own tables.
- jameshart 5y ago
  Dates aren’t natural. I know of at least three different date keys in different calendars that refer to the same calendar day - choosing to use one particular date standard to refer to days is choosing a pre-existing synthetic key, not a ‘natural’ one.
  - mulmen 5y ago
    Dates are natural keys. You seem to be conflating format and value. 1,234,567 and 1234567 are the same number. 19-Jun-21 and 2021-06-19 are the same date. Databases already handle this.
    - jameshart 5y ago
      19 June 2021 is also known as: Dhuʻl-Qiʻdah 9, 1442 AH 9 Tamuz 5781
      - mulmen 5y ago
        Great! Then if they are unambiguous they can be inserted directly or disambiguated and then inserted. At query time any format can be used to represent them to a user.
        jameshart 5y ago
        Ah, so you're suggesting instead of storing 'what a person calls this date' as the key, you instead convert it to some underlying representation that you can then choose to present in different calendars - like a UNIX epoch-relative offset or something. Congratulations, you just created another synthetic key for describing dates.

wombatpm 5y ago

In the 1980's your student ID was most likely your SSN

dragonwriter 5y ago

> The textbook case would be student IDs in a university database where each row corresponds to a single student “Student IDs” are an example of surrogate keys, though they may be surrogate keys in a system predating and outside of the DB. (And if the mapping between them and actual students are managed by an error-prone process outside of the DB, they probably aren’t good primary keys for a table of students.)

bart_spoon 5y ago

I think a natural key that I’ve encountered is a database containing professional NBA data. Teams never play more than once per day, and there are only two teams in the game, so a natural (composite) key for games is simply the date of the game and the two teams that played. You can create a “game ID” if you wish, but you aren’t actually doing anything but adding a column of redundant information.