> 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.
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...
> 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.
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.
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.
Actually in the summary he suggests to create column with uuid in each table and use it as primary key.
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.
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.
This results in them having the system believe they are two people, one of who hasn't filed returns and no easy way to fix it (apparently the easiest fix is to possibly create a company?).
This despite there being some kind of unique ID submitted in both systems, but that ID is not currently reconciled.
Not sure 100% on the details myself but I guess the moral of that story is, duplicate checks on the name itself may not even be a guarantee of uniqueness - even assuming there was only 1 person with that name!
More information in the Twitter threads: https://twitter.com/patio11/status/947821990810869762 https://twitter.com/marcan42/status/947862901725024257
I know the author is talking about a small scale project here, but shortcuts like this (in my experience) just lead to a mess later on; irrespective of the complexity of the problem being solved.
Especially since if you have any FK relationships, and that was the only candidate key for the members relation (and why have such a bad key if it's not the only one), you then need to add new attributes to create a new key, populate it to all existing roles, change all existing FK relationships and associated queries, on top of the easy part, which is dropping the old unique constraint.
I think the point was that for a 40 people club odd are very very poor that two people would have the same name. And even if so a club manager could still differentiate by adding a middle name or a nickname (in a 40 people scenario).
Of course IF your neighborhood club expand and you need to manage a lot of people you'll have to switch to a better technique. His point was that you must fit to you business case. (But hey some people change name when they marry so... problems can happen fast, but that's mutability issue not uniqueness).
Overall very good article but keeping critical mind as you did is needed.
Wrong. For example I have exactly the same name as my father and we're members of the same squash club.
A single counterexample does not falsify the theory that the odds are very very poor!
> I suspect that if we enlarged the sample, it would fall much further.
Try to back that up, maybe.
The thing that upsets me here is the idea that dropping the constraint later on is easy. It's only easy if you are a software engineer!
I quit shortly thereafter.
> This requirement is a willful violation of RFC 5322, which defines a syntax for e-mail addresses that is simultaneously too strict (before the "@" character), too vague (after the "@" character), and too lax (allowing comments, whitespace characters, and quoted strings in manners unfamiliar to most users) to be of practical use here.
> The following JavaScript- and Perl-compatible regular expression is an implementation of the above definition.
> /^[a-zA-Z0-9.!#$%&'+\/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)$/
To quote the DBA Stack Exchange post, "if it's good enough for HTML5, it's probably good enough for you". And if you're using inputs with `type="email"` in HTML5, you already are constraining your emails to that format.
(That said, it sounds like the regular expression you were asked to use was not a good one)
[2] https://html.spec.whatwg.org/multipage/input.html#valid-e-ma...
/^[a-zA-Z0-9.!#$%&'*+\/=?^_`{|}~-]+@[a-zA-Z0-9-]{1,63}(?:\.[a-zA-Z0-9-]{1,63})*$/
[1] http://referencesource.microsoft.com/#System.ComponentModel....
As an exercise, I deconstructed that into the pieces it was built from. Funny times :)
https://metacpan.org/source/RJBS/Email-Valid-1.202/lib/Email...
It does make me appreciate the one guy who can rise above all that, Paul Sellers.
Phone number, however, should be avoided as an ID. Far more likely to have phone number ownership changed than Google reclaiming your email address and give it to someone else realistically.
...then you're not building a system that works with the way users work. Some (very small) percentage of your users will have problems.
I first encountered this problem when I asked my parents to test my first startup. They shared an email account. When they signed up to things they just used the shared account. It was fine until they wanted separate accounts on the same service and it failed. It's not OK to assume everyone has a unique email address and that works as a reference to an individual. The assumption is incorrect. Email addresses are not unique to individuals.
A lot of services fail at this. If you're targeting older people then you shouldn't use a users email address as a username, and nor should you use it as a security factor.
When it comes to designing a system, I try my best to prioritize this way: security, privacy, and finally usability. A product with the best user-experience may have the most vulnerable design. I say this because we need to balance between security and UX. Pick some constraints which make sense to your system.
If we have to accommodate every corner case, the system will become extremely insecure and unreasonable. In your case, perhaps solve it with "sub-account" if that makes sense for your system. To Netflix, sub-account seems reasonable so Netflix gives that as an option for user to share his/her account with his/her family/buddies.
Actually, sorry if being a little out of bound, you probably should help your parents setting up unique email address for them. They want a separate account for some specific reasons, I assume, so why not help them?
Email was created TO be unique. It is we the users who decide how to use that email address, BUT it is not the system/service designer TO accommodate every use case.
That's the point - they didn't want different email accounts. They wanted to share one. There will always be edge cases that don't conform to your expectations of how users should be doing things, and your applications probably need to work even in those less-than-ideal circumstances. If your app is going to be used by older people who might share an email address then I wouldn't make the account process require a unique address for every user.
I actually go a step further these days and try to make things that don't even require any email address. The less personal information required to create an account the better. So long as users are aware that they won't be able to recover an account if they forget their password there's no real need to ask for an email in the first place.
Or do you expect old people that share an email account to gracefully handle unique usernames? I'm pretty skeptical of that one.
And "So long as users are aware that they won't be able to recover an account" sounds like a joke. Maybe one in twenty people will truly internalize that, if you're lucky.
To me, surrogate keys are just about always preferable over natural keys, but it's also important to think about the "naturalness" of natural keys from a user's pov. In some contexts, using a surrogate key while also enforcing unique names (with the rare possibility of a case where an admin has to go in and do something weird like add a genuinely identical name with a "2" after it, say) can be a better trade-off.
Absolutely do not do this.
People have names that are duplicates. A situation where someone is unable to join a club because their name clashes with an existing member is not OK.
Expecting a club administrator to be able to drop a uniqueness key from their database in order to resolve this situation is not a reasonable solution!