Email Address is a very common field and column for each and every application or system. As per application size and scope relevant database system store lots of user Email Addresses.
E.g. 10,00,000 email address is stored in one database system and this is also increasing day by day. Usually any database user stores all this email address into one column. Most probably there is one column name is EmailAddress Varchar(250) and user store email address like ” firstname.lastname@example.org ” into this column.
This is great and usually 70% to 75% user doing this.
Once database size and number of users go to increase day by day at that time this kind of email address storage will not perform as per expectation and decrease the performance of the database.
How this will decrease the performance of the database?
- As we assumed that email address stored into one column like ” email@example.com”.
- Now your CEO is required to find that, how many total number of users from only ” Yahoo ” domain?
- How many total number of users from only ” India Yahoo “?
- Require to set some additional offer only for ” Gmail ” domain.
Friends, now think to all above question.
In my career path I have also stored email address into one column, but gradually I came to know that this is not good to store into one column because now a day Email address is not only for user name or only for storage purpose. This is very important for promotional and marketing activity for any application.
When you have stored into one column and let’s imagine if you want to find only ” Gmail ” domain out of 10,0,00,000 email address. As database developer, you can think that you require one string operation on all stored email address in which you will find the part which is after ” @ “. But this is not a solution where string searching and parsing is required, which slow down the performance of relevant select statement.
Report possibility is an N number of but at least this kind of basic requirement of report we can manage by designing a proper structure to store Email Address.
The solution is simple,
Just divide your Email address into two part or three part.
- Single Part : ” firstname.lastname@example.org “.
- Two Part : ” myprofile” and ” yahoo.co.in “.
- Three Part : “myprofile” and ” yahoo ” and ” co.in “.
Below is a sample data.
tbl_User table Data:
Examine the first email address:
User Anvesh has email@example.com, Roy has firstname.lastname@example.org
As you can find the EmailDomainID reference column in, a User master table which is indicating that which email address is associated with User.
Now thing is going to easy:
If I require to find how many users with gmail then I require to find only EmailDomainID=1.
You can also apply easy extension filter like yahoo from co.in and .com.
This will give you 80% to 85% better performance than to store Email into one column.
Also, This will reduce the size of data, E.g, if we store ” Gmail ” keyword 10,00,000 times into the database, then it consume more space than to store only integer ” 1 “.
Yes, I also agree that, during every insert you have to split email address for storing purpose, but this is good instead of parsing string to count only domain name.
This structure is quite complex to manage at every insert and at every email existence check but it gives better performance.
Below is simple string operation.
Full Email Address is : email@example.com
Now parse only ” anvesh ” from firstname.lastname@example.org
(POSITION('@' IN 'anvesh@yahoo.CO.IN') -1)
Now parse only ” yahoo ” from email@example.com
,(POSITION('@' IN 'anvesh@yahoo.CO.IN') + 1))
,POSITION('.' IN SUBSTRING('anvesh@yahoo.CO.IN'
,(POSITION('@' IN 'anvesh@yahoo.CO.IN')))
Now parse only ” co.in ” from firstname.lastname@example.org
,(POSITION('@' IN 'anvesh@yahoo.CO.IN') + 1)))
,POSITION('.' IN SUBSTRING('anvesh@yahoo.CO.IN'
,(POSITION('@' IN 'anvesh@yahoo.CO.IN') + 1)))+1)
The above code is required whenever you insert the email address into the database because from the application end we receive one full email address, but for effective storage you have to parse like above code. You can also suggest three different input parameters from the application where above all code can shift to at application level.
This code is only for MySQL.
I hope this post will help you to store Email Address in Effective way.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.