1

It is very common to have to keep large collections of objects in databases. Very simple examples are the problem of keeping messages among users or the users (that have several properties) themselves in large social networks. Having said that, consider for instance that in your application you need to keep a large collecion of objects with several properties. There are some ways to do this:

  1. To keep the serialized version of the object using PHP:serialize.
  2. To keep each property of the object in a different column of the table.
  3. To create a XML version of the object and keep the string associated to it using PHP: SimpleXMLElement::asXML

Are there notable differences (vantages/advantages) among these procedures in terms of performance (memory/processing)?

Any help is very welcome!

Marty
  • 39,033
  • 19
  • 93
  • 162
DanielTheRocketMan
  • 3,199
  • 5
  • 36
  • 65

1 Answers1

2

Option 2, a column per property, is a far superior strategy to storing a serialized or otherwise structured string representation like XML1, for the simple reason that the objects represent data and the database is designed for storing and retrieving it in a relational way, all while separated from your application logic.

You are able to query values, combine them, join them against other tables representing other object types. The database is itself highly optimized to perform the querying, sorting, joining, etc which you might want to do in your application. You lose all of that if you store it in a serialized form, and the object's data remains entirely bound to your code where it must be unserialized or decoded to be reconstituted as an object and used, and worse, it remains frozen in its own data structure, unable to be easily related to other object types in your application.

This is one of the foundations of web application design. When data needs to have a persistent state, storing object properties to database columns wherein the rows may be instantiated as objects is an almost universal pattern in web applications and just about any application framework you may find in any programming language will implement this in a generally similar way. Object-relational-mappers (ORM) exist to automate the 2-way transition from database storage in tables to objects in your code.

If you are interested in exploring an ORM for PHP, this question details some well-regarded options. Additionally, I would recommend examining how a few PHP application frameworks approach their Model storage and retrieval - Symfony, Laravel, CakePHP, others. Some depend on an ORM like Doctrine, others use their own methods.

1 Purpose-built JSON document storage engines like CouchDB or MongoDB are in a somewhat different scope here, talking instead about just directly serializing PHP into or an XML column. When to use a dedicated NoSQL solution is a far broader topic with lots of opinions each way.

Community
  • 1
  • 1
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • thank u for your great answer. In the very particular case that I am keeping the list of friends of a social network in MySQL option 2 is still the best pattern? Pattern 1: serialized object with the friends of a user kept in a column the userdata table. Pattern 2: a different table with 2 columns with all relationships like: line 1: user 1, user 2. Line 2: user 3, user 5 ... – DanielTheRocketMan Feb 17 '14 at 13:22
  • 2 Is still better - commonly, you would have 1 table which identifies details about each user, and a table which joins users as friends, having a column for the userid and column for the friendid, both of which point back to the users table. That creates a many-to-many relationship where any user could be linked with any other user as friends. Each row in the joining table represents exactly 1 relationship between 2 friends. To unfriend someone, delete the row. – Michael Berkowski Feb 17 '14 at 13:44
  • thank you again!! (:-) I thought this seemed the best solution, but I wanted to check. This way is exactly the way I keep messages from one user to the other user. A table where each line is a message, which has the id of the message, the id of the sender, the id of the receiver, the date, the status (new, old), the subject etc... Thank you!! – DanielTheRocketMan Feb 17 '14 at 13:52