Is it good or bad practice to use the user email in two separate columns of the user SQL table?

Question

I'm fairly new to web development and I have a bit of a weird question.

I'm currently working on a personal project that may or may not eventually become a real, commercialized product.

In my database, I have a "user" table that stores the user's information, including their username, encrypted password, first name, last, name, email address, etc.

Now, I've decided that the usernames will always be email addresses since the users won't be able to interact with each other (therefore, no sensitive data security issues). The problem is I have both an "username" and an "email" column. As of now, when the user create their account, they don't type in an username and an email. They type in an email, which gets passed into the two SQL columns.

Later on, they can change their username and email address separately and both could technically be different. I realize now that this might not be ideal and rewriting the parts of the code affected by this, changing the database structure, etc. is going to to take a little bit of work, which I'm 100% willing to do, but not sure yet if it's necessary at all, so that's why I'm asking this question here.

Could this situation be causing issues in the future, either terms of data management, security management or simply user experience?

Answer 1

There is no sense of having redundant information by design. So the way you present the situation in the title calls for a no : it is not a good practice.

However, in the title you might misrepresent the situation and not take into account all the needs:

• conceptually a user account is not necessarily a user email. So strictly speaking, the two columns are not redundant.
• your decision of using email address as user account is a business rule that may very easily evolve if you don’t hard code it.
• people change email from time to time. How would you manage this ? The easy way is to let them login with their old email address, change their email, et voilà! That’s the only way to do it with one column. But what if they misspelled their new email? Good practice is therefore to ask them for their new email and send a message to the new address to confirm. With the 2 columns you can easily decide to change the user account to the new email only after the confirmation.

This last bullet demonstrates that despite your business rule, both fields may differ —at least temporarily— and for a good reason. This is a good argument to keep 2 different columns for 2 different purposes. It's not YAGNI (You're Ain't Going to Need It) but YGNIBDKY (You're Going to Need It But Don't Know Yet).

Finally there is a risk assessment to be done. It is relatively easy to merge two columns into one (actually it’s 1 search/replace; 2 if there’s a different naming in your table and your code). If later on you decide to revert to a more flexible naming, it’s much more difficult to separate the case where you meant the user id and the cases where you really wanted the email.

Answer 2

We shouldn't model database columns that we don't use — only define columns that are used in queries — not ones that you have to maintain but are not otherwise used, as unused columns add to your technical debt without future proofing your system.

See also @DocBrown's related answer, which, if I may summarize, says model what you need now, not what you think you may need later.

See also other arguments in YAGNI:

• https://www.martinfowler.com/bliki/Yagni.html
• https://blog.codinghorror.com/kiss-and-yagni/

Answer 3

I don't think there is any question that it's a bad idea to have two columns which are identical by convention but the convention is not enforced and not visible in the schema.

The more interesting question is whether or not to spend the effort to clean it up right now. This is known as tech debt. There are good reasons for not addressing tech debt, for example:

• The area of the code is going to be retired soon
• The project or area of the code is experimental and there is a likelihood it's going to be retired or replaced (pivot in lean startup speak)
• The area of the code has a very low rate of change and the code works
• The change is risky. For example, can you copy one column over the other, or do you have "legacy" data where the columns differ and it's not clear which one is correct.

Reasons to fix it usually are the inverse, for example there is a high rate of churn in the code and the tech debt is slowing down every change.