Is storing a delimited list in a database column really that bad?

Question

Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.

Now, I know that the correct solution would be to create a second table and properly normalize the database. It was quicker to implement the easy solution, and I wanted to have a proof-of-concept of that application quickly and without having to spend too much time on it.

I thought the saved time and simpler code was worth it in my situation, is this a defensible design choice, or should I have normalized it from the start?

Some more context, this is a small internal application that essentially replaces an Excel file that was stored on a shared folder. I'm also asking because I'm thinking about cleaning up the program and make it more maintainable. There are some things in there I'm not entirely happy with, one of them is the topic of this question.

question from:https://stackoverflow.com/questions/65625744/mysql-join-from-split-column

Answer 1

In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:

• Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
• Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
• Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
• Can’t delete a value from the list without fetching the whole list.
• Can't store a list longer than what fits in the string column.
• Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
  idlist REGEXP '[[:<:]]2[[:>:]]'^*
• Hard to count elements in the list, or do other aggregate queries.
• Hard to join the values to the lookup table they reference.
• Hard to fetch the list in sorted order.

To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.

Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational “optimization” benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.

---

^* MySQL 8.0 no longer supports this word-boundary expression syntax.