From time to time, I notice that user emails are duplicated. Even with model validation (application level) to verify uniqueness. I suspect this is due to concurrency issues.
I decided to add an index at db level to solve this problem.
A lower index probably makes sense anyway.
Even if we guarantee that emails will be reduced at the application level. The database provides a more reliable guarantee of consistency.
It may not matter, but using DB can not hurt either.
I can add many additional precautions to maximize the chances that none of the uppercase characters are added by the application, but the application still cannot control the data coming in to db from other sources, and all this code is prone to a developer error. service costs, etc.
class CreateUniqueIndexForUserEmails < ActiveRecord::Migration def up remove_index :users, :email execute <<-SQL CREATE UNIQUE INDEX index_users_on_lower_email ON users (LOWER(email)); SQL end def down execute <<-SQL DROP INDEX index_users_on_lower_email; SQL add_index :users, :email end end
I also associated this logic with this block of code in the user model:
def email=(value) write_attribute :email, value.downcase end
This ensures that FooBar@email.com is overwritten as FooBar@email.com (at the application level).
I read RFC online, mainly from pointers to Are email sensitive addresses? , which indicate that some mail servers care about case sensitivity.
Even when I send emails today. I almost do not do casing. In fact, I print the email address as a single. Email is still delivered.
Is RFC something very important? If the user enters FooBar@email.com , the application registers the email as FooBar@email.com . Will this have any effect on electronic deliverability?
If not, what other problems should I consider?