Storing arrays in the database

Jeanie Tallis picture Jeanie Tallis · Sep 9, 2011 · Viewed 9.3k times · Source

I'm wondering if it is actually good practise to store Arrays in the database ? I tend to use json_encode rather than serialize, but was just wondering if it is a good idea. If not, then I can make some small changes and just implode the array with a comma.

Answer

NullUserException picture NullUserException · Sep 9, 2011

No, it's a terrible practice. Please refrain from inserting CSV, JSON*, serialize() or ANY kind of serialized data in a relational database. Denormalization is almost always a bad idea - don't do it unless you really know what you are doing, or you'll start asking questions like: this, this, this, this, ...

Doing that, you lose or it severely hinders your ability to:

  • Use JOINs.
  • Find or modify a particular element
  • Enforce referential integrity
  • Benefit from index usage
  • And it also wastes space

It may sound pedantic, but seeing people do this is one of my pet peeves - especially in light of the plethora of questions asked on SO that would be avoided if they did the right way.

Here's the right way to do one-to-many and many-to-many relationships in an RDBMS.

*Although some SQL databases have built-in support for JSON, it's often better to restructure your data so that you don't need this