Best Practice: Put all fields in a db column or lump some into a serialized hash in a TEXT column?

rubyonrails-talk
1

On my previous countless Rails apps, I've always put each data field as its own column in the DB, but on the last 2 projects, I've started to create a catch-all column that is a MySQL TEXT that I serialize and treat as a hash for a bunch of general stuff, from arrays, hashes, etc.

This has worked well on the last 2 apps whose requirements were not firm, so there are lots of changes on the fly.

So when there is a data field for a model that I know I'm going to want to do a find_by..., order, or some other database operation on, I obviously put it into its own SQL column, but otherwise, I've been lumping everything else into that general serialized hash and setting up methods in the model to get/set them as if they were normal attributes.

I'm curious to hear some feedback on this technique, e.g. "that bad programming practice because..." or "it has a performance impact because..." or "hey, me too"

2

On my previous countless Rails apps, I've always put each data field as its own column in the DB, but on the last 2 projects, I've started to create a catch-all column that is a MySQL TEXT that I serialize and treat as a hash for a bunch of general stuff, from arrays, hashes, etc.

This has worked well on the last 2 apps whose requirements were not firm, so there are lots of changes on the fly.

So when there is a data field for a model that I know I'm going to want to do a find_by..., order, or some other database operation on, I obviously put it into its own SQL column, but otherwise, I've been lumping everything else into that general serialized hash and setting up methods in the model to get/set them as if they were normal attributes.

I'm curious to hear some feedback on this technique, e.g. "that bad programming practice because..." or "it has a performance impact because..." or "hey, me too"

I've done it, but I think in general it's probably a bad idea. If you're system changes that frequently the odds that a new business requirement of "oh hey that field we added last week that you put into the serialized blob we now need to search against" is going to bite you. Or, someone will want to interface into the database with some other language (ie. not Ruby) and they'll have problems reading that serialized data.

Where I have done it and I think works all right is for "user preferences"... little bits of mostly boolean flags or color choices that you'll never search on and will vary quite a bit as the preferences grow...

-philip