Storing information/email addresses as csv list in a column?

Question

I was thinking of storing e-mails as a csv list in a column of my table. This is to be used by my code which will extract the e-mail ids and then send emails to them.

I am also storing the SMTP server information in the same table. For each row, I have a from address, several columns for smtp server information and finally a to address which stores email ids as csv.

Is there any reason why one should not do this ?

What if you have to remove (or update) an email address? Finding such an address would be troublesome. — Aziz Shaikh, Jan 03 '14 at 20:01
Store an email address per row, eventually you will end up slitting the csv — Kevin Bowersox, Jan 03 '14 at 20:02
@AzizShaikh - Yes, I thought of that. In my case, there is might not be a need to change the address. If it does, then we can copy the old value and replace with new, right ? — Steam, Jan 03 '14 at 20:02
@KevinBowersox - Then, I will also have to repeat the SMTP information for each address OR i could map each mail id to a smtp row in a new table. — Steam, Jan 03 '14 at 20:04
Your best bet for future endeavors is to make essentially an email address table that is linked by id or some other field where you can store email addresses separately and as many as you'd like without the hassles of delimited data in a column. — shadowjfaith, Jan 03 '14 at 20:06
See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) — Bill Karwin, Jan 03 '14 at 20:43

score 3 · Accepted Answer · answered Jan 03 '14 at 20:25

With respect, this is an astonishingly bad idea. Don't do it. If you do, you will be sorry. I know because I've done it. It seemed like a good simplification when I did it, but I was wrong.

If you store some kind of junk in a column that needs to be processed with substring or regex processing, you will systematically defeat the most important purpose of any RDBMS system: the ability to search and update quickly.

Take the time to do this right. Create yourself an email table. Many systems don't allow emails to be duplicated, but others do. If your system doesn't allow duplication, use this table layout.

  email_address    varchar(255)   primary key
  user_id          reference to id number in your user table (guessing it's a user)
  user_order       a small integer for the 1st, 2nd, 3rd, email etc.

Put a compound index on (user_id, user_order)

If your system allows email duplication, use this table layout instead

  user_id          reference to id number in your user table 
  user_order       a small integer for the 1st, 2nd, 3rd, email etc.
  email_address    varchar(255)

Your primary key is the compound key (user_id, user_order). You should also put an index on email_address so you can find particular ones.

Either of these tables may also need a name field, because lots of email addresses are structured like this:

           "Neuman, Alfred E." <aen@madmag.com>

See Appendix A of RFC822 for more examples.

Storing information/email addresses as csv list in a column?

1 Answers1