Is storing a list of strings in single database field a bad idea? Why?

Question

Recently, I started to work on some legacy system. People that developed it, came to an idea to store list of strings in single field of database table. Lets say that it is an identifier for object that does not have any representation nor data in database. The range of that identifiers will be relatively small in production.

On the other hand, my intuitions and "good design taste" tells me that it should be represented in separate table (similar to a table used for representing many-to-many relations).

Is their approach really bad and it would be better to start a refactoring? If yes, what bad consequences the original design can cause in future? Are there any relational design principles that explain that approach?

Edit to response for comments:

As I suppose, they didn't use this approach to solve some specific problem like hierarchical structuring in a tricky way. The most probable scenario was the case that they were simply working under time pressure and needed to implement new features as quick as possible.

I am sure that previously the field represented single value. They were going to implement feature to store more then one value and tried to avoid database migrations.

Answer 1

The datamodel isn't normalised; to be so it would need a separate table as you say. In that regard, it's not particularly good datamodelling practice.

Whether it was done for a good reason or not is difficult to determine. Conceivably, coding simplification or performance may have been motivators. As likely is that the field originally contained one identifier, requirements changed and the devs didn't have time or inclination to re-factor.

Probably more important is whether or not you should refactor yourself. In similar circumstances I would not pre-emptively refactor a case like this by default. I would consider it if one of the following applied:

1. you have evidence that this causes problems e.g. from legacy issue logs
2. you know for a fact that that you'll be making functional changes in that area
3. the code that handles the data is particularly complex and difficult to reason about.

What I would do, and TBH I would recommend this whenever you take over a legacy application, is start a wiki (or equivalent) and document cases like this. For example,

• issues you've found such as the datamodelling wrinkle
• changes you plan to implement
• changes you don't plan to implement but would if there were time
• areas of code that are difficult to reason about
• areas of code that you've found hard to maintain.

I've found that this is a useful aide memoire for me as I work in and/or come back to a codebase. It also can be very helpful to your successor when they, in turn, need to start learning the codebase.

Answer 2

Is storing a list of strings in single database field a bad idea?

It would generally be considered a violation of normalization.

However, sometimes this is used a solution to a problem, e.g. in hierarchical structuring, where a variable length path string of some sort represents structure.

Among the problems with a list of items in a single string can be:

• in query, this means using string searches instead of relational calculus; indexing the data can be problematic.
• there is the question as to the meaning of the ordering of the entries in the list, and that you more than likely cannot enforce anything on ordering as a constraint on the db.
• there is the issue of separator character, and the potential for character escaping/un-escaping problem with the individual items.
• there is the potential for duplicate entries in the same list; again this stems from not being able to directly enforce constraints (though maybe a trigger function could check constraints).
• a single item alone is still a list, but might be mistaken as not since we cannot tell (or ask) the database that the true type is a list. This can be problematic if most rows have only one item in the list, when some have more than one: there's no way to enforce proper usage of the column as a list.

Answer 3

It's a common antipattern to do this.

Your requirements change and now you need more values in a place where you used to only need one. Like a book has only one author right? Who could have guessed a book has multiple authors? This is an easy way to fulfill this requirements change without having to change your database schema.

There are some downsides though.

• Queries become more difficult because now you have identifying data combined in 1 field.
• You can no longer use "=" but have to use something like "like". Which will kill performance.
• You lose the ability to join on that field.
• Try count/sum etc, it won't work.
• Updating, becomes awkward.
• You get like artificial limits because you chose a varchar(10) to hold your commaseparated list.
• and more.

So basically, please don't do this.

Basically you are taking out the "relational" in "relational database".

Answer 4

There are plenty of arguments for our being a bad idea already. I think it would be fair to add some reasons why it would be a good, or at least OK idea. Not sure how many of these apply intis specific case, but it seems that at least done performance remarks are relevant:

• if the numbers and length of strings is strictly limited, than the performance difference should be negligible. At least for some edge cases, the performance will be better, as you don't need the join.
• depending on the main usage of the field, this form may be easier to handle.
• if the list is ordered, and the data doesn't require foreign keys, list fields are far superior to whatever relational database can provide in this regard.
• being simple piggy-backing on existing singular field may be a prudent choice in systems where schema migration is costly. It is certainly a technical debt, but it may be the kind that is worth taking and never paying back, even if you need to bleed some interest now and then.

When attempting a refactoring, it is always a good idea to first understand the reason behind the previous design choices. Make sure that the conditions and requirements has indeed changed enough to warrant the cost and risk.