Storing arrays in the database

Asked 9/9, 2011 at 16:37 Answered 9/9, 2011 at 16:40

php database serialization json denormalized

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.

Wallaby answered 9/9, 2011 at 16:37 Comment(4)

You're storing the whole array in one column ? How do you intend to run sql statements to search for entries ? – Rebate 9/9, 2011 at 16:38

It's a small array of file names, it's not multi-dimensional or anything. It's a package containing files to be combined (css and js but not together of course) ... the intention isn't to search for a specific file. You specify the files on index.php for example, and it'll return the corresponding package id. Of course, the problem is that if you specify the same files in a different order - it'll mess up. It's important the orders are fixed. Do you get what I mean? – Wallaby 9/9, 2011 at 16:43

Neither json_encode Or serialize is in array, are you refer to store the string? – Trona 9/9, 2011 at 16:44

Well yeah, it's pretty much an array in string form. – Wallaby 9/9, 2011 at 16:49

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}

Acidosis answered 9/9, 2011 at 16:40 Comment(6)

They'll be no need to edit the package, other pages that rely on certain files may end up turning faulty. The only time you'd really edit it, is by making a typo in a file name, in which I can then see why storing an array like this is not beneficial. – Wallaby 9/9, 2011 at 16:55

@Jeanie If you are really sure about this, I should just go with JSON. CSV is unreliable and serialize() is very easy to break and way too verbose for my taste – Acidosis 9/9, 2011 at 17:2

I am currently using json_encode ... ajreal edited my post to say CSV and I have no idea what a CSV is. – Wallaby 9/9, 2011 at 17:4

@Jeanie Comma Separated Values – Acidosis 9/9, 2011 at 17:5

Ah. Yeah that was going to be my other option if using json_encode, and serialize were bad ideas. I thought it would be the next best option. – Wallaby 9/9, 2011 at 17:7

Denormalization is almost always a bad idea - don't do it unless you really know what you are doing Exactly! Denormalization should not be done thinking about imaginary performance boost. First, you always must keep normal form and if and only if you face some performance issues you should look at... NO not denormalization but things like indeces, query hints and reorganizations, materialized views etc. Denormalization is the last thing to do in your pursue of performance. – Rode 8/12, 2011 at 13:20

Depends on your usage pattern. If you're going to need to access smaller portions of the array (e.g. for use in a where clause or similar), then it's a bad idea - you lose all the benefits of storing data in a relational database by making the data un-relatable. You'll end up with major overhead extracting that small piece of data over and over and over again.

On the other hand, if you're just using the database as a data store and never need to slice that stored array apart - just insert and retrieve, then there's probably no problem at all, other than maybe waste of space, as a serialized/json'd format tends to be "wordy" and take up more space than the raw data itself does.

Loren answered 9/9, 2011 at 16:40 Comment(1)

Json format is smaller than serialized format, at least for my size of arrays. Which is one of the reasons I prefer json. Though yes, it's simply to store... and retrieve once per page (maybe not even per page, depending on the package) – Wallaby 9/9, 2011 at 16:51

Recommended topics

Hot tags