Store multiple values in the same database column, how far is it worth it?

Question

Store multiple values in the same database column, how far is it worth it?

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I realize that a lot of people run into this question at some point in development, "Do I put the values separated by a comma in the same column? Or do I create an auxiliary table?", both questions have their points to analyze.

I would like to use this post, to generate content that will add 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 handling and organizing data, but in a way ends up taking up more space in the DB when the information flow grows.

The use of multiple values in the same column, I think saves space, but on the other hand, it makes SQL more complex and data more difficult to handle. What would be the most interesting model to follow when faced with a situation like this?

sql

asked by anonymous 01.08.2018 / 07:19

2 answers

Jquery scroll () IE Compatibility How to delete all JPG files inside a folder using PHP

score 1 · Answer 1

It all depends on how much information you want to store in a single column and what you ultimately want to do with it. If it's only a few values, it might be worth it, since the space it will occupy in the database and the "work" to break the string, treating it and understanding it will be minimal.

But if your column is of type TEXT or NTEXT (or even VARCHAR(MAX) ) and store a lot of information (separate yes but a lot in it) you can (almost certainly) performance in queries where to include this column (creating indexes can be a problem), and it will be much more difficult to perceive the content of the information and sometimes very difficult to handle the information (the change of a index can have unwanted effects in code!).

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

score 0 · Answer 2

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

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

Eg: For a table where I store the MDF-e ( Manifesto of Electronic Tax Documents ) issued . One of the requirements of this electronic tax document is a sequence of tags called - "Transport Unit Seals". This information is a simple sequence of strings (a tag called "Sealing Number"), in which I do not need to make queries (SQL WHERE clause) or any type of relationship (JOINS). That is, I only use it to display in a grid on the screen and later to generate the MDF-e XML.

Result:

In the database I store this: "123 # 456 ## 789 # ..."

Generating xml would look like this:

-Case2-CreatinganewtablewithforeignkeyandothernecessarydataIdecidebycreatinganewtable,whenthesituationiscontrarytotheexampleofCase1.Thatis,thedataismulti-valuedandIneedtodoconsultationsandjoinsinthefuture.

Eg:ContinuingwiththeMDF-eusecase.AnotherrequirementofthisDF-eisthegenerationofasetoftags,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 ()

Result: In the database, you would create a table: "MDFeInfCIOT":

Generatingxmlwouldlooklikethis: