4 ms·

That's not the only point of the article I really disagree with: > Here are some values that often work as natural keys: > login names This makes user nam

That's not the only point of the article I really disagree with: > Here are some values that often work as natural keys: > login names This makes user names static. Which would annoy people if they change name (eg marriage, gender change, nationalization change (Chinese name vs English name)) or they just want to update their online handle. > email addresses Same problem as above. What happens if someone wants to change email addresses (eg they used a work one and changed jobs, or they got a new email to combat spam on the old one, or a new email address to reflect a name change (see above)) > mac address on a network This makes more sense than above, but MAC addresses can be spoofed or even just used as a proxy, so you would need to be careful that the information you're uniquely storing against the MAC address is intended to be unique. Most use cases that immediately springs to my mind wouldn't follow his unique rule but I'm sure there will some examples that do. > (lat,lon) for points on the earth That might work if you also included an elevation point as well but office and apartment blocks (for example) will often have different tenants at the same latitude and longitude coordinates. > Some people seem to struggle with the choice of “natural” key attributes because they hypothesize situations where a particular key might not be unique in some given population. This misses the point. Actually no it doesn't. That's entirely the point as it demonstrates good planning and future-proofing rather than setting arbitrary business rules that are hard to rectify if and when you do run into those particular edge case scenarios.

Sean1708 9y ago
>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.
- laumars 9y ago
  If i understand the article correctly it's discussing using natural keys as primary keys. Eg if you're using login_name as a natural key then that's presumably replacing a more traditional user_id. So it would be hard not to use login_name as a foreign key in that scenario. Where as if you still had a user_id as your primary key, you could still have user_id as a unique key (most RDBMS I've used support "unique keys") to enable the business logic discussed but without encouraging it's usage as a foreign key. I think the article did touch on the fact that you can have multiple keys, but given it's heavy emphasis on finding natural keys over the more traditional logic of incremental integers, I can only assume the point of it wasn't about additional unique keys but rather alternatives for primary keys.
  - Jwarder 9y ago
    > If i understand the article correctly it's discussing using natural keys as primary keys. I'm not sure about the author's intent here. In the prior section they dismiss the necessity of primary keys. However, the author rails on about unique keys. I'm not familiar with PostgreSQL, but I assume every reference to "key" is effectively an index with a unique constraint. If that is true then all of the discussed issues with unique names are the same even if the author doesn't care about primary key in particular.
  - Gustek 9y ago
    From how I understand it the author does not make any suggestions to use natural keys as primary key. He just defines what is natural key. And he does mention that they may change and it has to be considered. Actually in the summary he suggests to create column with uuid in each table and use it as primary key.
- openasocket 9y ago
  I agree, I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. If it's a forum or has commenting capabilities, your handle is your absolute identity. If it's not a forum, no one ever really sees your username, so there's little motivation to change it.
  - laumars 9y ago
    Twitter does (though I don't know of anyone who has). As does Google and Facebook. Github too. Away from web services, gaming platforms like Nintendo's Switch allow for user name changes. As does the more enterprisey databases like ActiveDirectory (Windows logins), ldap and Linuxes / Unix passwd table also allows for login name changes. To be honest, I've encountered more systems that do let you change your user name than systems that don't.
  - dragonwriter 9y ago
    > I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. Pretty much any system with a real name policy both does and needs to let you change your handle, because preferred real (and legal) names change. Now, these systems often use email addresses as login names, but they sometimes also support changing the primary email address as well. In effect, identity is most likely managed by a surrogate key that isn't exposed.
  - banderman 9y ago
    eBay allows this. It's useful for brand management.
  - bmm6o 9y ago
    Stack Overflow lets you change your name.
- xiconfjs 9y ago
  if you are linking the login name to other tables, author of a forum/board post, it gets complicated, after the login name changed...
  - deleted 9y ago
    [deleted]
arundelo 9y ago
RFC 2392 Message-ID values are supposed to be unique, but they may not be if the software generating them is buggy. I remember reading somewhere about some email program that assumed they were unique (in effect using them as a natural key). If you were unlucky enough to get messages with identical Message-ID values, all but one would disappear.
SatvikBeri 9y ago
> This makes more sense than above, but MAC addresses can be spoofed or even just used as a proxy, so you would need to be careful that the information you're uniquely storing against the MAC address is intended to be unique. Most use cases that immediately springs to my mind wouldn't follow his unique rule but I'm sure there will some examples that do. As one example, there was a bug in one version of CyanogenMOD that caused many phones to broadcast the same MAC, even when connected to Wifi. In some cases, this would cause different devices to kick each other off of a connection. See e.g. https://forum.xda-developers.com/tmobile-lg-g3/help/2x-lg-g3-cm12-mac-address-wifi-issue-t3039699 https://forum.xda-developers.com/tmobile-lg-g3/help/2x-lg-g3...

Sean1708 9y ago

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

laumars 9y ago
If i understand the article correctly it's discussing using natural keys as primary keys. Eg if you're using login_name as a natural key then that's presumably replacing a more traditional user_id. So it would be hard not to use login_name as a foreign key in that scenario. Where as if you still had a user_id as your primary key, you could still have user_id as a unique key (most RDBMS I've used support "unique keys") to enable the business logic discussed but without encouraging it's usage as a foreign key. I think the article did touch on the fact that you can have multiple keys, but given it's heavy emphasis on finding natural keys over the more traditional logic of incremental integers, I can only assume the point of it wasn't about additional unique keys but rather alternatives for primary keys.
- Jwarder 9y ago
  > If i understand the article correctly it's discussing using natural keys as primary keys. I'm not sure about the author's intent here. In the prior section they dismiss the necessity of primary keys. However, the author rails on about unique keys. I'm not familiar with PostgreSQL, but I assume every reference to "key" is effectively an index with a unique constraint. If that is true then all of the discussed issues with unique names are the same even if the author doesn't care about primary key in particular.
- Gustek 9y ago
  From how I understand it the author does not make any suggestions to use natural keys as primary key. He just defines what is natural key. And he does mention that they may change and it has to be considered. Actually in the summary he suggests to create column with uuid in each table and use it as primary key.
openasocket 9y ago
I agree, I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. If it's a forum or has commenting capabilities, your handle is your absolute identity. If it's not a forum, no one ever really sees your username, so there's little motivation to change it.
- laumars 9y ago
  Twitter does (though I don't know of anyone who has). As does Google and Facebook. Github too. Away from web services, gaming platforms like Nintendo's Switch allow for user name changes. As does the more enterprisey databases like ActiveDirectory (Windows logins), ldap and Linuxes / Unix passwd table also allows for login name changes. To be honest, I've encountered more systems that do let you change your user name than systems that don't.
- dragonwriter 9y ago
  > I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. Pretty much any system with a real name policy both does and needs to let you change your handle, because preferred real (and legal) names change. Now, these systems often use email addresses as login names, but they sometimes also support changing the primary email address as well. In effect, identity is most likely managed by a surrogate key that isn't exposed.
- banderman 9y ago
  eBay allows this. It's useful for brand management.
- bmm6o 9y ago
  Stack Overflow lets you change your name.
xiconfjs 9y ago
if you are linking the login name to other tables, author of a forum/board post, it gets complicated, after the login name changed...
- deleted 9y ago
  [deleted]

arundelo 9y ago

RFC 2392 Message-ID values are supposed to be unique, but they may not be if the software generating them is buggy. I remember reading somewhere about some email program that assumed they were unique (in effect using them as a natural key). If you were unlucky enough to get messages with identical Message-ID values, all but one would disappear.

SatvikBeri 9y ago

> This makes more sense than above, but MAC addresses can be spoofed or even just used as a proxy, so you would need to be careful that the information you're uniquely storing against the MAC address is intended to be unique. Most use cases that immediately springs to my mind wouldn't follow his unique rule but I'm sure there will some examples that do. As one example, there was a bug in one version of CyanogenMOD that caused many phones to broadcast the same MAC, even when connected to Wifi. In some cases, this would cause different devices to kick each other off of a connection. See e.g. https://forum.xda-developers.com/tmobile-lg-g3/help/2x-lg-g3-cm12-mac-address-wifi-issue-t3039699 https://forum.xda-developers.com/tmobile-lg-g3/help/2x-lg-g3...