How "bydlokodersky" approach is the storage of serialized arrays in SQL?

Question

MySQL

The elder, let's say, office mate, all the time tries to "those data that is not really needed, but needs to be stored" (which, however, can then be used in various reports), put it into a serialized array and store it as an attribute in SQL. Personally, this seems to me a "shit-coded" approach, because why do we use RDBMS at all then, after all, you can store it in files. Hence the question, even rather a survey - how much do you, dear users of the toaster, consider this ... mmm .... normal? Share your thoughts
Shl.: Those pkhpshnikam who think that once it is possible, then it is necessary, I say right away, you can not write anything. If, in essence, you understand that this is exactly what you need to do for some reason, or you don’t need it - wellcome!
ZY2.: The rest of the database consists of about 50 tables, classified and designed as at least people like Kodd or Data say (you can trust them, right?)). Therefore, in addition to the fact that I don’t find storing data in such a database in a serialized form, it also seems to me at least not aesthetic
ZY3 .: this is not Big Data, there are about 1500 records (just a cloud of fields for each record)

Answer 1

In an ideal world where ponies poop like butterflies, of course, this cannot be done.
In our world, this is, for example, in Wordpress - the most popular CMS in the world.
Sure, WordPress has some pretty ugly code, but that doesn't stop them from being super successful.
Let's go garlic. A normalized table is cool, but often very expensive. To throw the data of the serialized array into a cell and then get it - 10 minutes of the programmer's work.
Designing a good database is orders of magnitude more difficult (and requires programmers with higher qualifications).
And the saddest thing is that the second option for 1500 records is not needed. It is possible and to store in files, in general that. But with the database there will be some cool features out of the box. If stored in files, you need to write ORDER or SELECT yourself, which takes some time.
Therefore, from a business point of view, the "it will do" approach is more profitable in terms of money, although it offends your sense of beauty.

Answer 2

It is quite stupid to evaluate the "shit-coding" of your approach just because you store the array in a non-normalized form. To see this, it is enough to recall the very concept of normalized data and think about its essence. Here's a head-on example for you: for some reason you don't say that storing a string in a database is bad. And it, in theory, can be represented as an array of characters and normalized so that one row of some table will store ONE character. Nonsense, you say? Yes, for most tasks this is nonsense (although perhaps not for all). Simply because NOBODY needs to extract from the base a PART of a string, any subset of its characters. In most tasks, a string is taken as an atomic (!) value, and that is why _that's why no one tries to store it character by character. We have only one useful criterion - what are atomic values ​​for your task? All.
For some reason, everyone believes that until you normalize "to hell", the designed base is no good. Yes, of course, normalization is important, it even makes sense to normalize "with a margin", as mentioned above - in case some data is subsequently also filtered and processed at the database level using SQL. However, if you clearly understand that in the near future you are not going to work with the array element by element (at the SQL level), then storing it in its entirety will only benefit.
Still, they use JSON and XML data types in SQL databases, and nothing. And blobs are used. Because if the designer knows what is planned to be processed in queries and what is not, then he knows and to what extent the data needs to be normalized.
alarm_sugave a great example with the user's config. Why try to project its fancy structure (for example, hierarchical) onto a relational database, if it is easier to store it in its natural form (JSON / XML / plaintext) and write it entirely to the database?
PS By the way, an array can be stored not in text form, but in binary form in a BLOB, then it will take up less space, and no issues with encodings.

Answer 3

Everything depends on the task. An example is a user config. There is practically no point in storing it in a relational structure. No one will do any statistics or search on this config. Adding a new property for the config is to make a checkbox in the template, figuratively speaking. With one request they pulled out serialization, got an array with parameters .. PROFIT!

Answer 4

Why not just normalize the database to get rid of the array? Well, or alternatively, postgres has a data type for storing json objects that can be searched. If the data does not have a fixed structure, then why not store it in a file or use something like mongodb.

Answer 5

This is purely theoretically acceptable only if one or both of the following statements are true:
1) This data is not systematized at all, it is not known what may need to be stored next time, how many parameters will be, what format they will be, etc.
2) This data will never require complex searches.
At the same time, the question arises - what for in general to shove such data into a SQL table?
Logs in my opinion it is more expedient to store in a file.
If this is some kind of complex user data, well, create a separate table for them, adding fields as needed.
If this is completely unstructured data, you should think about a documentary database.

Answer 6

Personally, this seems like a "shit-coded" approach to me.

You are completely right.
But such shitcode is acceptable (but it doesn't stop being shitcode) if the serialized array is an atomic value at the DBMS level.
If you need it not all, or part of it, then it is absolutely unacceptable.

Answer 7

The situation is not simple, for good it is better not to resort to this storage method, it is still considered an architecture vulnerability and in most cases it is better to normalize the database. But every developer in practice has a situation when a choice arises whether it is really necessary, and this choice must be made thoughtfully and based on the characteristics of the project, and perhaps serialization will turn out to be quite the right decision.
Let me give you an example - in the organization card there is a position on the map parameter, it is described by several characteristics: the coordinates of the point, the coordinates of 2 opposite corners of the map and the scale of the map, whether they need to be stored in separate fields of the table when they are inseparable from each other, I think absolutely not.
But in your situation, everything can be different, here you need an experienced team leader who can foresee possible problems in the future, specifically for this project, make a decision whether to prohibit or allow such storage.

Answer 8

Got a project in which some of the data was stored in the form of serialized arrays.
Stored e-mail lists tied to cities. Each array contains about a dozen addresses.
Problems encountered:
1. Search and output in other systems
2. Quickly fix something - I do not argue, it's not right, but sometimes the situation requires it.
3. Collection of statistics.
In the end it had to be redone.

Answer 9

Serialization 100% will bring problems, if you store objects in 1 field, then JSON / XML is better.
What do you (more precisely, they) want to save using this approach?
PS It seems to me that a good ORM will be the solution to any problem in which you want to store serialized objects, or is there no such thing in PHP?

Answer 10

There are certain advantages to this method, but they are not many. Only one immediately comes to mind, doubtful, but still.
One of my projects (on php, oh yes) works in several languages ​​at once - Russian, English, Ukrainian and German. The web part has a news feed, usually several lines (they say there was an update today, we apologize for the brakes) and so as not to create a column or table for each language - I store a serialized array with 4 elements - each element = news text in its own language .
I will not argue how true this is, but I think it is convenient.

Answer 11

Why can't everyone see anything scary?
This is a disgusting approach. It is not necessary to choose them today, but tomorrow it is necessary. And, besides, it's longer - there will be more crutches, writing another model faster than remembering this crutch every time.

Answer 12

The approach takes place when you have unstructured data.
The only downside of this approach is that you will not be able to use the serialized data in queries normally + you need to take into account the change in the structure of the data being written over time (i.e. your program must be able to work with all versions of the data stored in the database).