> Many systems ask the username to fulfill all three of these roles, which is probably wrong.
What system with any non-trivial level of use uses the text username as (1) the FK in the database, as opposed to the generated or auto-incremented ID in the db; (2) the login name; and (3) the publicly-displayed displayed "name" of the user for others to see?
Plenty of forums etc use the login name for #2 and #3, and I'm not convinced by this article that that's the wrong way to do it. I haven't ever seen a single professional product that uses the text username that a user logs in with as the actual DB-level foreign key. That's grade school level database design.
When logging in, how do you get that autoincremented ID column? Some more complex variant of "SELECT *.id WHERE username = $1". So functionally, yes the username is the root identifier that pulls the very first record that then allows other joins to occur. But you are right, the username column is not literally the key being joined on.
There is also the security issue that by having the login name also be the publicly displayed name lowers the bar for attempting to make a targeted attack on the site, as well as other sites where the attacker suspects the victim may be using the similar login name. This can particularly be true in cases of harassment across platforms, which while is not a computer science security issue, it is a personal psychological security issue.
> the username column is not literally the key being joined on
That's exactly the point though. If you join on the username than allowing emails/usernames or whatever that identifier is to be edited is very hard. How you identify the row to auth against is literally the point of a username.
Though I haven't seen too many systems where the static login name is actually hidden from the world. It seems to just end up being the canonical ID, so right there in every permalink or whatever.
What system with any non-trivial level of use uses the text username as (1) the FK in the database, as opposed to the generated or auto-incremented ID in the db; (2) the login name; and (3) the publicly-displayed displayed "name" of the user for others to see?
Plenty of forums etc use the login name for #2 and #3, and I'm not convinced by this article that that's the wrong way to do it. I haven't ever seen a single professional product that uses the text username that a user logs in with as the actual DB-level foreign key. That's grade school level database design.