WTF 101: Serialized array strings in MySQL DB
-
Apologies to those who like to guess the WTF, but to clarify, I have had the "pleasure" of handling a system that uses a database to store serialized array structures. These in turn can contain anything including XML and the kind of tabulated values you would expect to be in, say, tables.
I'm sure somebody can think of a reason why you would store serialized data in a database, instead of using mongo. Maybe I don't know something about mongo or MySQL which somehow makes this a good idea, maybe there's a way to pull out relevant fragments of the serialized string from inside the database field.
Please enlighten me. Otherwise, name and shame: ProcessMaker
-
Why a (relational) database instead of mongo? Perhaps they already used MySQL, and if you've already got a database, there's little need to add another one.
Why store serialized data? Because you don't intend to query the data, just store and retrieve. Especially with a relational database, storing arrays in tables is a lot more work than just storing a string in a blob, and a lot slower too.
-
@TGV said:
Especially with a relational database, storing arrays in tables is a lot more work than just storing a string in a blob, and a lot slower too.
Here's a nickel, buy yourself a better database.
But basically, +1.
-
@Shoreline said:
I'm sure somebody can think of a reason why you would store serialized data in a database, instead of using mongo.
Or you could actually structure your data and use, say, many-to-many table relationships. Mongo doesn't do anything that SQL doesn't, it's just more convenient when dealing with APIs that don't return a set schema (like Twitter or some other newfangled web 2.0 REST APIs.) I'd never recommend it for any other task until it matures.
@Shoreline said:
maybe there's a way to pull out relevant fragments of the serialized string from inside the database field.
Only extremely WTF ways. If you end up with a badwrong database like this, you're better off loading the entire field and parsing it in your app. Sadly.
-
@flop said:
@TGV said:
Especially with a relational database, storing arrays in tables is a lot more work than just storing a string in a blob, and a lot slower too.
Here's a nickel, buy yourself a better database.
RIP, 1NF
-
@Shoreline said:
I'm sure somebody can think of a reason why you would store serialized data in a database, instead of using mongo. Maybe I don't know something about mongo or MySQL which somehow makes this a good idea, maybe there's a way to pull out relevant fragments of the serialized string from inside the database field.
-No Mongo: because they don't know it exists. When you only have a hammer, all problems are nails.
-Arrays in columns: because they don't know how to model a relation in a RDBMS or do a join
-
-
@clom said:
-No Mongo: because they don't know it exists. When you only have a hammer, all problems are nails.
I know Mongo exists and I'd still never use it.
@clom said:
-Arrays in columns: because they don't know how to model a relation in a RDBMS or do a join
It could make sense if, as TGV pointed out, you're never querying on it and representing the whole thing in normalized form would be a pain.
-
I am intimately familiar with this database design antipattern. The excuses always boil down to "I am not confident about my MySQL skills so this seemed easier", and it always turns out to be worse in the long run than using databases properly. It's "Expert Beginner" territory.
-
@clom said:
When you only have a hammer, all problems are nails.
Or maybe you only have a hammer, and you just need to screw this in, but the screwdriver is too far away and you don't feel like getting up so you just hammer it in and hope no one complains.
-
@spamcourt said:
@clom said:
When you only have a hammer, all problems are nails.
Or maybe you only have a hammer, and you just need to screw this in, but the screwdriver is too far away and you don't feel like getting up so you just hammer it in and hope no one complains.
When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy.
-
@Lorne Kates said:
@spamcourt said:
@clom said:
When you only have a hammer, all problems are nails.
Or maybe you only have a hammer, and you just need to screw this in, but the screwdriver is too far away and you don't feel like getting up so you just hammer it in and hope no one complains.
When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy.
When the only joke available is a "When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy." joke, every joke in this thread follows the form of a "When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy." joke joke.
-
@Ben L. said:
@Lorne Kates said:
@spamcourt said:
@clom said:
When you only have a hammer, all problems are nails.
Or maybe you only have a hammer, and you just need to screw this in, but the screwdriver is too far away and you don't feel like getting up so you just hammer it in and hope no one complains.
When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy.
When the only joke available is a "When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy." joke, every joke in this thread follows the form of a "When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy." joke joke.
When meta humor.
-
@Ben L. said:
When the only joke available is a "When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy." joke, every joke in this thread follows the form of a "When the only analogy you have is the "when the only tool you have is a hammer every problem looks like a nail" analogy, every point of confusion you encounter will look like it can be explained with an "'when the only tool you have is a hammer every problem looks like a nail' analogy" analogy." joke joke.
PARSE ERROR. UNEXPECTED "@Ben L. said:
HAMMER_NAIL = "when the only tool you have is a hammer every problem looks like a nail"
When the only joke available is a
When the only analogy you have is the
HAMMER_NAIL
analogy, every point of confusion you encounter will look like it can be explained with an
HAMMER_NAIL
analogy
analogy.
joke, every joke in this thread follows the form of a
When the only analogy you have is the
HAMMER_NAIL
analogy, every point of confusion you encounter will look like it can be explained with an
HAMMER_NAIL
analogy
analogy.
joke joke.FTFY.
-
@clom said:
When you only have a hammer, all problems are nails.
When you only have a relational database, force all your data to be relational, whether it needs to be or not ? That would seem to be just another class of hammer to me.
-
@spamcourt said:
@Ben L. said:
HAMMER_NAIL = "when the only tool you have is a hammer every problem looks like a nail"
When the only joke available is a
When the only analogy you have is the
HAMMER_NAIL
analogy, every point of confusion you encounter will look like it can be explained with an
HAMMER_NAIL
analogy
analogy.
joke, every joke in this thread follows the form of a
When the only analogy you have is the
HAMMER_NAIL
analogy, every point of confusion you encounter will look like it can be explained with an
HAMMER_NAIL
analogy
analogy.
joke
joke.FTFY.
FTFY.
-
@daveime said:
When you only have a relational database, force all your data to be relational, whether it needs to be or not ? That would seem to be just another class of hammer to me.
Until very recently, all data sources were relational.
-
@joe.edwards said:
@flop said:
@TGV said:
Especially with a relational database, storing arrays in tables is a lot more work than just storing a string in a blob, and a lot slower too.
Here's a nickel, buy yourself a better database.
RIP, 1NF
My exact thoughts when I saw this, but there are some use-cases where array doesn't break 1NF. It would be interesting to see the postgres developers rationale behind this.
-
@GNU Pepper said:
It's "Expert Beginner" territory.
For those who haven't already come across the term (and there appears to be at least one): Dunning–Kruger effect. (Or stage 1 of this)
-
@dtech said:
It would be interesting to see the postgres developers rationale behind this.
It's there in the article:They can be used in ways which break first normal form but mere use of an array does not necessarily break first normal form's atomicity requirement because arrays differ in significant fundamental ways from relations.
- i.e. they know it's fucked up, but they nevertheless ignore/"reason away" the cognitive dissonance that arises because of it.
-
@PJH said:
@dtech said:
It would be interesting to see the postgres developers rationale behind this.
It's there in the article:They can be used in ways which break first normal form but mere use of an array does not necessarily break first normal form's atomicity requirement because arrays differ in significant fundamental ways from relations.
- i.e. they know it's fucked up, but they nevertheless ignore/"reason away" the cognitive dissonance that arises because of it.
Huh? That's not what I saw there. The justification provided was that you can use arrays as matrices. And then they show how you could solve simultaneous linear equations. I'm not sure how useful this would be, but it does seem to have a purpose that does not, in fact, affect 1NF, which is an ideal, in any case, and shouldn't be a hard and fast rule.
I think we could probably all agree that this probably gets abused more often than used as described.
-
@boomzilla said:
I think we could probably all agree
You must be new here.
-
@joe.edwards said:
@boomzilla said:
I think we could probably all agree
You must be new here.No, he's right. We could probably all agree. We won't. But we could.
So you're wrong. Jerk.
-
@Lorne Kates said:
@joe.edwards said:
@boomzilla said:
I think we could probably all agree
You must be new here.No, he's right. We could probably all agree. We won't. But we could.
So you're wrong. Jerk.
I disagree. We are fundamentally incapable of consensus.
-
@morbiuswilters said:
@clom said:
-No Mongo: because they don't know it exists. When you only have a hammer, all problems are nails.
I know Mongo exists and I'd still never use it.
@clom said:
-Arrays in columns: because they don't know how to model a relation in a RDBMS or do a join
It could make sense if, as TGV pointed out, you're never querying on it and representing the whole thing in normalized form would be a pain.
Why would you NEVER use mongo? It is queryable you know, unless you mean something more specific. Regarding normalization, it is a document-oriented DB so of course; do you reject all document DBs or just mongo?
-
@clom said:
It is queryable you know, unless you mean something more specific.
What? Of course it's queryable. Why are you telling me this?
@clom said:
Regarding normalization, it is a document-oriented DB so of course; do you reject all document DBs or just mongo?
Mongo is not nearly solid enough to trust with data. I generally don't need a document DB, because there are ways to accomplish the same goals with a relational DB which is rock-solid and trustworthy. However, if I needed a simple persistent key-value store, I'd prefer redis.
-
@clom said:
Why would you NEVER use mongo? It is queryable you know, unless you mean something more specific. Regarding normalization, it is a document-oriented DB so of course; do you reject all document DBs or just mongo?
I'm going to come forward and admit that I don't know why anyone would ever choose a NoSQL database over a proper RDBMS. This could just be because of my own ignorance, so please, tell me when and why I should.
-
@joe.edwards said:
I'm going to come forward and admit that I don't know why anyone would ever choose a NoSQL database over a proper RDBMS. This could just be because of my own ignorance, so please, tell me when and why I should.
When you have large amounts of unstructured data (or where such structure as is had really isn't very relational) then you're not gaining anything much from a relational DB. Some other type of store could make a lot of sense in that situation (faster storage, faster retrieval, that sort of thing). Examples of things that don't fit well with relational databases are hierarchical documents (HTML, PDF, XML, DOCX, etc.), complex graphs (RDF, the sort of stuff when you analyze social networks) and images.OTOH, if you can get a nice relation or two out and populate a table with that (without weird stuff like having one column of a table linked to another column of the same table via a “foreign” key relation) then you're in a position to do very well with an RDBMS.
-
@dkf said:
@joe.edwards said:
I'm going to come forward and admit that I don't know why anyone would ever choose a NoSQL database over a proper RDBMS. This could just be because of my own ignorance, so please, tell me when and why I should.
When you have large amounts of unstructured data (or where such structure as is had really isn't very relational) then you're not gaining anything much from a relational DB. Some other type of store could make a lot of sense in that situation (faster storage, faster retrieval, that sort of thing). Examples of things that don't fit well with relational databases are hierarchical documents (HTML, PDF, XML, DOCX, etc.), complex graphs (RDF, the sort of stuff when you analyze social networks) and images.OTOH, if you can get a nice relation or two out and populate a table with that (without weird stuff like having one column of a table linked to another column of the same table via a “foreign” key relation) then you're in a position to do very well with an RDBMS.
Balance this with the fact that it's another piece of infrastructure to manage. Another system that needs backups and monitoring and recovery procedures. An entirely new set of concerns with regards to high-availability and data durability. All of that stuff adds up. So often it does make more sense to just use an RDBMS, even if it's not quite "correct".
-
@dkf said:
@joe.edwards said:
I'm going to come forward and admit that I don't know why anyone would ever choose a NoSQL database over a proper RDBMS. This could just be because of my own ignorance, so please, tell me when and why I should.
When you have large amounts of unstructured data (or where such structure as is had really isn't very relational) then you're not gaining anything much from a relational DB. Some other type of store could make a lot of sense in that situation (faster storage, faster retrieval, that sort of thing). Examples of things that don't fit well with relational databases are hierarchical documents (HTML, PDF, XML, DOCX, etc.), complex graphs (RDF, the sort of stuff when you analyze social networks) and images.OTOH, if you can get a nice relation or two out and populate a table with that (without weird stuff like having one column of a table linked to another column of the same table via a “foreign” key relation) then you're in a position to do very well with an RDBMS.
Yes, apart from when there is just too much data, document-oriented DBs and more generally what Martin Fowler calls "aggregate-oriented DBs" are great for simplicity when your app almost always fetches the same few aggregates. That category includes key-value and column-oriented DBs as well.
@morbiuswilters said:
@clom said:
Yeah, i misunderstood your point.It is queryable you know, unless you mean something more specific.
What? Of course it's queryable. Why are you telling me this?
@morbiuswilters said:@clom said:
Regarding normalization, it is a document-oriented DB
so of course; do you reject all document DBs or just mongo?Mongo is not nearly solid enough to trust with data. I generally don't
need a document DB, because there are ways to accomplish the same goals
with a relational DB which is rock-solid and trustworthy. However, if I
needed a simple persistent key-value store, I'd prefer redis.Well, i have used Mongo on a small project centered on a natural aggregate and no need to fetch across documents, and it worked fine. I can't swear it is as robust as say Oracle/SqlServer though, because i never did more. It is true that it is difficult to entrust your data to a new store, but i was asking if you had specific reasons to mistrust mongo more than another NoSQL Db.
-
@blakeyrat said:
@daveime said:
When you only have a relational database, force all your data to be relational, whether it needs to be or not ? That would seem to be just another class of hammer to me.
Until very recently, all data sources were relational.
You must never have had the privilage of working with the competing theory to relational databases: multi-valued databases. I once had the privilage of extracting data from a UniVerse database, when it was part of IBM, now part of Rocket U2. Of course, I got to access it through a buggy ODBC driver that ended up making it look like relational database views, so I didn't have to truly understand how to work with it natively.
Since it actually aligns better with XML type data structures who knows. Maybe Multi-valued DBs will make a full comeback.
-
@joe.edwards said:
@Lorne Kates said:
@joe.edwards said:
@boomzilla said:
I think we could probably all agree
You must be new here.No, he's right. We could probably all agree. We won't. But we could.
So you're wrong. Jerk.
I disagree. We are fundamentally incapable of consensus.
[...]
No, you want room 12A, next door.
-
@flop said:
@joe.edwards said:
@Lorne Kates said:
@joe.edwards said:
@boomzilla said:
I think we could probably all agree
You must be new here.No, he's right. We could probably all agree. We won't. But we could.
So you're wrong. Jerk.
I disagree. We are fundamentally incapable of consensus.
[...]
No, you want room 12A, next door.
Hey look I found a video that is a lot funnier and more entertaining that Monty Python.
-
@Ronald said:
@flop said:
Sometimes Ronald appears almost sane. Then he goes and posts something like this and obliterates any semblance of sanity.SHUT YOUR FESTERING GOB, YOU TIT! YOUR TYPE MAKES ME PUKE! YOU VACUOUS TOFFEE-NOSED MALODOROUS PERVERT!!!
[...]
No, you want room 12A, next door.
Hey look I found a video that is a lot funnier and more entertaining that Monty Python.
-
@HardwareGeek said:
@Ronald said:
@flop said:
Sometimes Ronald appears almost sane. Then he goes and posts something like this and obliterates any semblance of sanity.SHUT YOUR FESTERING GOB, YOU TIT! YOUR TYPE MAKES ME PUKE! YOU VACUOUS TOFFEE-NOSED MALODOROUS PERVERT!!!
[...]
No, you want room 12A, next door.
Hey look I found a video that is a lot funnier and more entertaining that Monty Python.
I have yet to find ONE person that likes Monty Python and that matches none of the following:
- Ugly
- Fat
- Irritating laugh (either girly giggles or snorty mucus scraping)
- Severe acne
- Wears Fruit of the loom briefs
- Wears white socks while not doing sports (California residents are exempt)
- Has the same glasses since high school (and/or has glasses with very large lens)
- Likes woodworking
- Is a boat model enthusiast (including ship-in-a-bottle)
- Runs with his arms flailing
Those are not to be confused with typical geek characteristics (arduino hobbyist, gamer, linux user, old hardware collector, etc). Monty Python aficionados are part of a low-quality subculture which has tarnished the reputation of the geek culture for too many years. I won't stand for it anymore.
IF YOU LIKE MONTY PYTHON YOU ARE NOT INVITED TO MY BBQ THIS WEEKEND
-
@Ronald said:
I have yet to find ONE person that likes Monty Python and that matches none of the following:
- Ugly
- Fat
- Irritating laugh (either girly giggles or snorty mucus scraping)
- Severe acne
- Wears Fruit of the loom briefs
- Wears white socks while not doing sports (California residents are exempt)
- Has the same glasses since high school (and/or has glasses with very large lens)
- Likes woodworking
- Is a boat model enthusiast (including ship-in-a-bottle)
- Runs with his arms flailing
I like Monty Python and none of those are true.
@Ronald said:
(arduino hobbyist, gamer, linux user, old hardware collector, etc).
Now, see, those five are really lame.
-
@morbiuswilters said:
I like Monty Python and none of those are true.
But you're a pudgy hobbit, so the first three have got to be true.
But my friend, however, also likes Monty Python, and he certainly doesn't match any of those. So there's the one person!
Wait... he's done metalworking (an interesting minimalist chess set, even) and built some physical things (several times indeed powered by Arduino). Does that count under the woodworking item?
Wait... he plays bass. Does that count under the woodworking item?
-
@dhromed said:
Wait... he plays bass. Does that count under the woodworking item?
That counts more as fishing, so no.
-
@clom said:
...i was asking if you had specific reasons to mistrust mongo more than another NoSQL Db.
or is it just blanket open-source hating?
-
@dkf said:
@dhromed said:
Wait... he plays bass. Does that count under the woodworking item?
That counts more as fishing, so no.Actually interview, clueless old guy interviewing musicians
"So, you play bass . . ."
"No, actually I play trout. "
-
@clom said:
@clom said:
...i was asking if you had specific reasons to mistrust mongo more than another NoSQL Db.
or is it just blanket open-source hating?
I have nothing against open source. It's like Midnight Basketball; it keeps certain people busy so they don't have the energy to go out and do bad things.
-
@morbiuswilters said:
@Ronald said:
(arduino hobbyist, gamer, linux user, old hardware collector, etc).
Now, see, those five are really lame.
Either you consider "etc" an item or you obviously can't count for shit, not sure which is worse
-
@Ronald said:
@morbiuswilters said:
@Ronald said:
(arduino hobbyist, gamer, linux user, old hardware collector, etc).
Now, see, those five are really lame.
Either you consider "etc" an item or you obviously can't count for shit, not sure which is worse
The list was zero-indexed.
-
@dhromed said:
But you're a pudgy hobbit, so the first three have got to be true.
What? No way! I'm beautiful and have an awe-inspiring laugh.
-
@morbiuswilters said:
@dhromed said:
But you're a pudgy hobbit, so the first three have got to be true.
What? No way! I'm beautiful and have an awe-inspiring laugh.
<br>
-
@Ben L. said:
@morbiuswilters said:
@dhromed said:
But you're a pudgy hobbit, so the first three have got to be true.
What? No way! I'm beautiful and have an awe-inspiring laugh.
<br>
-
-
@dhromed said:
@morbiuswilters said:
an awe-inspiring laugh.
Awe as in awful.
"Awful" literally just means "full of awe".
-
@morbiuswilters said:
@dhromed said:
"Awful" literally just means "full of awe".@morbiuswilters said:
an awe-inspiring laugh.
Awe as in awful.
And? Languages change over time and that word has dropped the original literal meaning long time back.
-
@morbiuswilters said:
I have nothing against open source. It's like Midnight Basketball; it keeps certain people busy so they don't have the energy to go out and do bad things.
Whatever, i guessed right.
