Is it good to store comma-separated values in single column?

Question

I know this question has probably been asked several times before, but my question is specific to my situation.

I am implementing a notification micro service. It has an email table and a queue table. The service is exposed using a REST API. Each email can have multiple to addresses and cc addresses. The queue has an email ID. Once the email is sent, the row is removed from the queue.

At this time, there is no requirement to query on who sent what emails or whether a email for a particular user with a given email address has been sent. There are multiple ideas on how to/cc email addresses can be stored.

1. Store them in JSON format in a address field of the email table:

    {
         toList: ['h@h','g@g'],
         ccList: ['a@b','c@d']
    }

I considered using the JSON data type of MySQL 5.7, but it's not supported in h2db which we use for integration tests.

2. Store them as comma separated values in toList, ccList columns in the email table.

3. Store them in a relational table such as email, emailaddress, emailaddresstype where the emailaddress table would contain the emailid, email address and the emailaddressstypeid. This results in several records in emailaddress table and also multiple joins.

What would be the best solution to implement in this case? I am generally against violating FNF and I'm not sure what it means to have so many records in an emailaddress table such as a record for every email sent to, for example, 2 to 3 people. There is no user table in this system, if that matters.

Answer 1

I suppose it really depends on what you plan to do with the data. If email addresses are nothing more than lists of strings the you will retrieve and manipulate somewhere else, then comma-separate strings in separate columns ("to", "cc", "bcc") is probably fine.

If you think you might want to query at some point and ask "who was CC'd on Email #1234 but was NOT CC'd on the reply message (#5678)?" it might be difficult to do that with simple comma-separated lists. In that case, you should go with separate tables to properly store the email addresses.

Answer 2

Is it good to store comma-separated values in single column

Generally speaking, No.

Accessing delimited data values embedded within a larger field generally results in [very] poor application performance. It almost always results in "Table Scans" because indexes can't be meaningfully used.

At this time, there is no requirement to query on who sent what emails or whether a email for a particular user with given email address is sent.

Lucky you.

Wait until you get into a dispute with a user who insists that they're not getting the emails that you [claim to] have sent them, at which point you'll have to start querying it. OK, if those queries table-scan it's not the end of the world if you only have to run a few of them but as soon a this sort of thing becomes common-place, you'll need to move to a more sensible data structure.

OK, preparing for this sort of usage flies in the face of the Developer's "YAGNI" mantra but, all too often, DBAs find themselves having to take a far longer-term view and making sure the data structures that they implement are as flexible as possible; sensible Normalisation is a big step in that direction.

Answer 3

You should store the data in normalized form (option 3) unless you have a compelling reason not to. Non-normalized data as in option 1 and 2 have a number of drawbacks: risk of inconsistencies, difficulties in parsing and querying and so on.

You are making the system more complex and brittle, so you need a good reason to do so. Some legitimate reasons might be caching complex data structures for performance reasons or storing arbitrary data which is opaque to the application.

But you don't mention any particular reasons justifying non-normalized data, so go with 3.

(You suggest a drawback of option 3 is more tables and more joins. But this is a red herring, since you have the same amount of data and underlying structure with the other options, it is just stored in a less convenient and optimized way.)

Answer 4

The first antipattern that Bill Karwin discusses in his book "SQL Antipatterns" is Jaywalking (because an intersection is avoided). The antipattern specifically lists out the problems associated with storing comma separated values when you need to use them separately (for instance, querying the table becomes complicated). The solution offered in the book chapter is straight forward - create an intersection table.