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

by

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[[:>:]]' or in MySQL 8.0: idlist REGEXP '\\b2\\b'
  • 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.
  • Hard to choose a separator that is guaranteed not to appear in the values

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.

More Related Contents:

  1. What are database normal forms and can you give examples? [closed]
  2. Normalization: What does “repeating groups” mean?
  3. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  4. have address columns in each table or an address table that is referenced by the other tables?
  5. Minimum no of tables that exists after decomposing relation R into 1NF?
  6. What’s the better database design: more tables or more columns?
  7. What’s the difference between identifying and non-identifying relationships?
  8. Can I have multiple primary keys in a single table?
  9. How are super- and subtype relationships in ER diagrams represented as tables?
  10. Difference between scaling horizontally and vertically for databases [closed]
  11. NoSql vs Relational database
  12. Best practices for storing postal addresses in a database (RDBMS)?
  13. What is the most efficient way to store tags in a database?
  14. Microservices with shared database? using multiple ORM’s? [closed]
  15. Why use a 1-to-1 relationship in database design?
  16. What is the optimal length for an email address in a database?
  17. What are the advantages of using a single database for EACH client?
  18. Database optimization orders
  19. In what way does denormalization improve database performance?
  20. Normal forms – 2nd vs 3rd – is the difference just composite keys? non trivial dependency?
  21. Alternatives to Entity-Attribute-Value (EAV)?
  22. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  23. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  24. Limit an sqlite Table’s Maximum Number of Rows
  25. When we need to use 1-to-1 relationship in database design?
  26. Should OLAP databases be denormalized for read performance? [closed]
  27. Best beginner resources for understanding the EAV database model? [closed]
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment