4

In an off-hand comment on this question about storing PHP objects in a MySQL database I mentioned that it is probably not a good idea to do so.

My reasoning is that MySQL is explicitly a relational DBMS, and that it doesn't make sense to force it to be something else, as it will not be optimize for that.

I would assume that it would severely limit the power of SQL - every action such as combining data from different tables would now be moved to the application layer -, and I would assume that it will lead to a rather significant loss of performance - the whole object would now need to be fetched each time, and so on.

The OP of that question said that their teacher still recommended storing whole objects in MySQL - and there are multiple questions about this on stackoverflow, so I'm assuming they are not the only one doing it - , so I'm wondering if there are indeed any advantages to this approach, and if I correctly identified the downsides of this approach.

Note that I'm not asking about the benefits of NoSQL / Object DBMS, but about (mis)using a relational database to store objects.

Improve this question
1
  • 1
    A whole generation (maybe 2) has the twisted indistructable notion that everything should be stored in a RDBMS. They thnk rows and columns from the day they created their first application. Their Pavlov-response to starting a new project is to run to their favorite RDBMS and produce a schema because that should always be the basis for any system that deals with any kind of data. Performance and proper layering will often suffer. There may be security reasons for this approach though. Or accounting reasons, because they spent a fortune on a product X license which has to be... accounted for.
    – Martin Maat
    Commented Aug 21, 2016 at 6:51

1 Answer 1

Reset to default
3

You would store an object in a database record when you need the benefits that doing so provides. There are valid use cases for that, otherwise there would be no such thing as object databases or object-relational databases.

When would you store objects in a relational database?

  1. When your data is schema-less; that is, the objects can contain arbitrary fields. Storing an object means you can put whatever you want to in that object

  2. When you want to store an object graph of any kind.

  3. When the search and join capabilities of a relational database are of secondary importance to the flexibility of storing objects.

SQL Server allows you to store serialized objects in JSON or XML format, and then run first-class SQL queries on them. They claim that you would use these capabilities when:

  • Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future.
  • Your data represents containment hierarchy, instead of references among entities, and may be recursive.
  • You want to query into the data or update parts of it, based on its structure.
Improve this answer
1
  • It's interesting that you wrote about storing entire object graphs. I feel like this is exactly how some languages implement database-backed sessions on web servers.
    – Greg Burghardt
    Commented Aug 21, 2016 at 1:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.