Doctrine: saving you some tables and headaches

October 17th, 2013

I like database schema normalization, but sometimes it just creates too much overhead.

For example, you may want to let your users to enter a number of URLs (facebook, linkedin, twitter, blog, company, etc.) The straightforward solution seems to add a Url table with foreign key (user_id=1) to link it to the User. Sometimes you want to reuse the Url table for other entities, such as Project or Organization. In this case, you'd probably go with a Url table containing the name of the entity to which it belongs and an id (entity="User", entity_id=1), which requires me to have some extra logic in my code.

But then I step back and ask myself: "What do I gain by storing URLs in a separate table?" Usually, the answer is "Nothing, really." I often serialize such data to a text column using JSON:

[
  { "type":"twitter", "url":"http://twitter.com/afilina" },
  { "type":"linkedin", "url":"http://linkedin.com/in/afilina" },
  { "type":"company", "url":"http://foolab.ca" },
  { "type":"company", "url":"http://confoo.ca" }
]

It's very natural for me to work with this format, saves me tables and has a positive effect on performance, since I no longer require LEFT JOINs. For years, I wrote my APIs in a way that automatically deserialized JSON when loading from the database and serializing before storing back.

I recently discovered that Doctrine 2 has a nifty column type called "json_array". You basically interact with your model's property as though it was a regular PHP array like this $user->getUrls() and $user->setUrls(). Doctrine then takes care of the serialization and deserialization for you, which makes adding new columns of this type faster and easier than ever.

Reference of Doctrine mapping types: http://docs.doctrine-project.org/en/latest/reference/basic-mapping.html#doctrine-mapping-types

Happy coding!

Previous: I don’t judge a speaker by their GitHub profile Next: Ever wondered how ConFoo creates the perfect timetable?