Storing multiple values in the same column of the database, how much is it worth?

Question

I realize that many people encounter this doubt at some point in their development, "Do I put comma-separated values in the same column? Or create an auxiliary table?" Both questions have their points to analyze.

I would like to use this post, to generate content that will aggregate not only to my knowledge, but also in the study of several people who at some point came across, or will come across this situation.

The creation of an auxiliary table, has some advantages in the treatment and organization of the data, but in a way ends up occupying more space in the BD when the flow of information starts to grow.

The use of multiple values in the same column, I believe, generates a space saving, but on the other hand, it ends up making SQL more complex and data more difficult to handle. What would be the most interesting model to follow, when we come across a situation like this?

Answer 1

It all depends on how much information you want to store in one column and what you ultimately want to do with it. If it is just a few values, it may be worth it, since the space you will occupy in the database and the "work" to break the string, treat it and understand it will be minimal.

But if your column is the type TEXT or NTEXT (or even VARCHAR(MAX)) and store a lot of information (separate yes, but a lot in it) may (almost certainly) have problems with performance in queries where to include that column (index creation may be a problem), in addition to which it will be much more difficult to understand the content of the information and sometimes very difficult to handle the information (changing an index may have unwanted effects on the code!).

In my opinion it is always better to create new tables, normalize the information and use SQL correctly to connect the information in the form of keys, besides being a much more "correct practice".

Answer 2

I agree in part with the opinion of our colleague João Martins. I usually use this feature in my solutions, and I get the following criteria to decide between creating a new table or storing the data in a single column:

- Case 1 - Creation of a column and separate storage by wildcard character Simple information (not multi-valued)

Ex.: For a table where I store MDF-e (Electronic Tax Documents Manifesto) issued. One of the requirements of this electronic tax document is a sequence of tags called - "Seals of Transport Units". Such information is a simple string sequence (a tag called - "Seal number"), in which I do not need to make queries (clausa WHERE do SQL) nor some kind of relationship (JOINS). That is, I only use to show in a grid on the screen and later to generate the MDF-e XML.

Upshot:

In the store bank like this: "123#456##789#..."

When generating xml would look like this:

- Case 2 - Creation of a new table with foreign key and other necessary data I decide to create a new table, when the situation is contrary to the example of Case 1. That is, the data are multi-valued and need to make queries and joins in the future.

Ex.: Continuing with the use case of MDF-e. Another requirement of this DF-e is the generation of a set of tags, called - "CIOT Data". In this case, this grouping of tags, has in its interior a group of specific information - Unlike Case 1, where there was only a simple sequence of data ()

Upshot: In the bank, create a table: "Mdfeinfciot":

When generating xml would look like this: