To me tech debt is the result of callous introduction of invariants.
An inexperienced engineer keys your users table by their email. Suddenly a user changes their email address, and now you either refactor to use an autogenerated user ID (as you should have in the first place), or you have a huge brittle process for migrating a user between emails, or your entire codebase starts getting littered with statements looking for previous email addresses and copying data on the fly.
The mistake was that email=user identity is not a true invariant.
> The mistake was that email-user identity is not a true invariant.
That's true. I also wonder if there's an issue with lack of modeling experience or strategy. Natural key vs surrogate key is a really important distinction to make when modeling first party data. I've observed a common preference towards the latter (with uniqueness constraints) as a default. I think that's because generally it's more useful (and common) than not to be want to insert new things into your collection with monotonically increasing unique primary key values autogenerated without you having to do any of the accounting. I think natural keys are still useful, just in a RO/projection/ETL based use case.
> you have a huge brittle process for migrating a user between emails, or your entire codebase starts getting littered with statements looking for previous email addresses and copying data on the fly.
It's so unfortunate how easy of an error this is to make and how much it makes any future work on the codebase shittier. Eventually, it all needs to be refactored to regain any measure of sanity. I see so many companies that are bitten by this. Once it gets going, it becomes really awful for the engineers that have to maintain it. Morale takes a hit and attrition amps up. So much net human suffering that could have been avoided by hiring competent people to do the upfront work in the first place when the code was being written.
I used to have an Amazon.com account under my@email.com.
When Amazon arrived to my country, I was encouraged to migrate, but Amazon.mc didn't have the same selection.
After a while I wanted to buy a laptop from Amazon.mc and registered under a phone number.
Last week my music keyboard broke and I wanted to buy a digital piano from Amazon.mc but they insisted now that I register an email address. And then my email address is already in use.
Login ids should NEVER be case sensitive. What would happen if you allowed admin, Admin, ADMIN as different accounts, or JohnSmith, johnsmith etc. that would be a security nightmare.
And yet Unix* has case sensitive logins, and is renowned for its security.
*recent Unixes, since older Unixes only had support for upper case characters.
But I see your point, and it looks like it's not a clearly cut question. But I'm still leaning towards case sensitivity, because passwords are case sensitive, so it's easier to train users to consider both of login and password as case-sensitive. And it's a shame that some e-mail providers caved in and started making e-mail case insensitive…
P.S.: Should we also forbid the O/0, l/I characters in logins, like base58 does, because they can be ambiguous ?
P.P.S: And Unicode has added a whole new level of issues to this. Of course ASCII-only logins are completely unacceptable these days –
(think of users that don't have a latin-like alphabet as their native one)
– but I've recently ran into the issue where a service didn't properly normalize my password, so the µ from my previous keyboard layout ended up being considered as different from the μ from my new keyboard layout !
> Who doesn't have an auto increment Id field on every table?
In large organizations with a lot of teams, natural primary keys make interoperability easier across databases, as opposed to a forest of artificial keys that are all different. SQL experts who advocate natural keys point to ON UPDATE CASCADE as the solution to natural keys that occasionally change.
So the absence of an auto increment Id field might be a dumb error, or might be super smart, or might be both. Both means you're a SQL expert but haven't made sure everybody else working on the project always will be.
> Who doesn't have an auto increment Id field on every table?
Anyone who exposes their id fields, e.g via APIs, and doesn't want other people gaining insights into their volumes by watching the rate of increase in id values.
I've seen a situation where old IDs were re-used for new users, like we were about to run out of numbers or something… XD
And with former users coming back, of course expecting to have their IDs back too !
This being on a paper-first (often paper-only) database, with no locking process, where we also had the issue of new users being added at the same time, for the same ID.
If only I already had been through a database course at the time…
An inexperienced engineer keys your users table by their email. Suddenly a user changes their email address, and now you either refactor to use an autogenerated user ID (as you should have in the first place), or you have a huge brittle process for migrating a user between emails, or your entire codebase starts getting littered with statements looking for previous email addresses and copying data on the fly.
The mistake was that email=user identity is not a true invariant.