Storing an Address Without Breaking It

Stage: the address is in your database now. In control: you, the developer.

1. Two columns do the work of one

Store the address exactly as the person typed it, and compare against a separate normalised copy. Almost every address bug in a signup system comes from trying to make one value serve both purposes.

The stored original is what you display back, what you put in the To field, and what the person recognises as theirs. The normalised copy exists only to answer one question: is this the same address as one we already have? It is never shown and never sent to.

Once those two roles live in two columns, most of the decisions below stop being arguments. You can be aggressive about the comparison copy because nothing depends on it looking right, and conservative about the original because nothing depends on it being tidy.

2. The trimming has to happen before anything else

A handful of transformations are safe on the comparison copy and should be applied without hesitation.

Leading and trailing whitespace comes in constantly, because people copy addresses out of documents and chat windows and the selection picks up a space. Strip it. Zero-width characters and non-breaking spaces arrive by the same route and are worse, because they are invisible on screen and produce a mismatch nobody can see while staring straight at it.

The domain, meaning everything after the last at sign, is case insensitive by definition and should be lowercased. Nothing is lost, and it removes a whole class of duplicate rows.

The comparison copy is also where a unicode normalisation pass belongs, so that two byte sequences that render identically stop counting as different addresses.

3. Two normalisations look tempting and are wrong

Stripping the plus part and removing dots from the local side both appear in tutorials, and both quietly rewrite someone else's address.

The plus sign is a legal character in the local part, and the text after it is not decoration. People use it to route mail into folders and to know which company sold their address on. If you cut it before storing, their filters stop matching, mail lands in the wrong place, and they have no way of discovering that your form is the cause. Dots are the same story on providers where they are significant, and treating one provider's convention as a universal rule breaks every address on the providers with a different one.

The general principle is worth stating flatly: the part before the at sign belongs to the receiving mail server, and you do not get to reinterpret it. What is legal in that part and what forms tend to reject are covered separately in what counts as a valid address.

4. The local part disagrees with practice about case

The standard treats the local part as case sensitive, so Sam@example.com and sam@example.com are formally two addresses. In practice essentially every mail provider treats them as one, and users assume that too.

Following the standard here means two people can register what they both believe is the same address, and both of them will be right in different ways. Following practice means lowercasing the whole thing on the comparison copy, which is what nearly every working system does.

Take the practical route, and write it down in the schema rather than in a helper function somebody will forget to call. The unique index goes on the normalised column, not on the original. That single line is what prevents duplicates at the level where it cannot be bypassed, and it holds even when a second code path inserts a row without going through your signup service.

5. The column length has one correct number

An address is at most 254 characters in total. Not 320, which is the sum of the maximum local part and the maximum domain and cannot actually occur, and not 255, which is that number with an off-by-one.

The lengths that cause real damage are the small ones. A column declared at fifty characters looks generous until it meets a corporate address on a long department subdomain, and the failure mode depends on the database: silent truncation on some configurations, an error on others. Truncation is the worse outcome, because the row is written, the account exists, and the confirmation message goes to an address that does not.

Set the column to 254 and validate the same limit at the edge, so the refusal happens where you can produce a message rather than deep in a driver.

6. The domain is not necessarily latin

Internationalised domains exist, and an address arriving with a domain written in another script is not malformed. It has two representations: the unicode form the person typed and the ascii-compatible encoding used by the mail infrastructure.

Pick one representation for the comparison copy and convert consistently, otherwise the same address in its two spellings occupies two rows and neither one can log in reliably. Keep the typed form in the original column, as always.

This is a small population of addresses and a cheap thing to get right at the start. It is expensive to retrofit, because by then the duplicates already exist and merging accounts is a support problem rather than a code problem.

7. The duplicate surfaces at the worst possible moment

The failure this all prevents does not show up during registration. It shows up during password recovery, weeks later, and it is unpleasant precisely because of the timing.

Two rows exist for what the mail provider considers one address. The person requests a reset, your query returns the row they did not use, and the message goes to a real mailbox for an account that has none of their data in it. They now hold a working reset link into an empty account and are certain your system has lost their history. Support cannot tell which row is the real one either, because both are real.

The check that catches this is small: count rows whose normalised form collides. If that number is not zero on an existing system, the duplicates are already there and waiting for the next reset request.

The handover

The address is now stored in a shape that will survive the next five years, but storage is only the still picture. What moves is the account around it, and the states it passes through between the form and a working login are set out in the signup states nobody tests. Before it reaches your database at all, the browser has run its own opinion over the field, described in the check that runs in your browser, and if the normalised column is where you were planning to hang your primary key, the address as a user id explains why that costs more than it saves. If you want a live address to drop into your own form while the schema is still open in front of you, this generator will produce one on the spot.