Storing an E-mail Address

For my “Customer” table, I will using a person’s E-mail address as his/her “Username”.

This field will have a Unique Index as serve as the pseudo-Primary Key, although in reality there will be an “id” field that is an Integer, Auto-Increment as the true PK.

So the question, What is the best way to store an E-mail address?

I could just use a long, free-form data-type like VARCHAR(50), but where and how do I ensure that I’m getting legitimate e-mail addresses??

In addition…

1.) Is this something the Application should be checking or is it the Database’s responsibility?

2.) Should I use a Trigger or Stored Procedure to check things?

3.) Maybe Regular Expressions?

4.) I asked this an earlier post, but there is also the approach of taking the E-mail address that a User gives me, e-mailing them a link, and requiring them to “activate” their E-mail Address/Account so that I am 100% sure that I have a valid E-mail address and can reach them for important issues (e.g. Account Issues, Order Status, Balance Due, etc.).

TomTees

Correct that.

So I’ll set my “email” field as a VARCHAR(255)!!

TomTees

Even a varchar(255) can potentially be too small for some email addresses although those longer than that would be extremely rare.

If you need to be certain of being able to hold any email address no matter how long then you’d use a varchar(255) for the domain part and a separate varchar(64) for the local part and then assemble it as local part + ‘@’ + domain part in order to get the full address.

until you need to store one that’s slightly longer :slight_smile:

wikipedia:

The local-part of an email address may be up to 64 characters long and the domain name may have a maximum of 255 characters. However, the maximum length of a forward or reverse path length of 256 characters restricts the entire e-mail address to be no more than 254 characters.

Ha ha.

So using a VARCHAR(50) should suffice for my needs. And then according to your other answers, I should validate the e-mail’s format and existence in my application.

TomTees

beats me

i just thought i would answer your original bolded question[indent] What is the best way to store an E-mail address?[/indent]in a manner that might prove useful to other readers of this thread

:slight_smile:

Good to know.

Or I can make my form not accept anything larger than 255 characters!!

(Because anyone who would have an e-mail that long and/or want to have to type that in as a Username everytime they log in is not someone we would sell a T-Shirt too!!!) :wink:

TomTees

In my case, why would I want to search and sort by domain?

My purpose for using the E-mail is just to serve as a Username to identify the Customer.

Knowing that 37% of my customer use Verizon e-mail probably isn’t of much use.

Maybe you had another idea?

TomTees

1.) email validity should be checked outside the database
2.) no
3.) no
4.) as you wish :wink:

the only suggestion i have is that you store the email in two parts – the address and the domain

you can stuff the @ back in while concatenating them when you pull out the rows

having the domain separate makes it easier to search and sort