[development] serialized data in the DB Re: [support] signup_form_data

[Greg Knaddison - GVS]

On 7/13/06, Morbus Iff <morbus at disobey.com> wrote:
> > a:2:{s:4:"Name";s:6:"brenda";s:5:"Phone";s:10:"5555551234";}
>
> That is what is called "serialized" data, generated by serialize(). More
> documentation at http://us2.php.net/serialize (read the comments for a
> more verbose explanation). The above is equivalent to this PHP code:
>
>   array('Name' => 'brenda', 'Phone' => '5555551234');
>
> only in a database-friendly format.

It's in a format that can be stored in a database, retrieved,
unserialized, and used in PHP, but I wouldn't call it "database
friendly".  Maybe database storable.

Database *friendly* would be making a column for Name and a column for
Phone so that other tables in the database could do things like joins
on the data.

My perspective is clouded by being more comfortable on a sql> prompt
than in a PHP file, but serialized data in the DB makes me shudder
nearly every time I see it.

Sorry for the relatively OT rant, but while there are shortcut
benefits on the PHP side to doing things like serializing data and
storing it in the DB it can lead to inefficiencies and handcuffs in
the long run.  Databases tend to go really slowly for queries that
look like

WHERE field LIKE '%Name%\'Brenda\'%'

Which is both slow and can generate false matches if the serialized
array is sufficiently large. Or a clause like

some_table INNER JOIN signup_form_data on some_table.Name =
RIGHT(LEFT(field,index(field,'\',\'Phone),len(LEFT(field,index(field,'\',\'Phone))-index(LEFT(field,index(field,'\',\'Phone),'Name\'=>'))

Which is slow, can generate false matches, and is relatively
obfuscated for what I'm trying to do when compared with a query like:

some_table INNER JOIN signup_form_data ON some_table.Name =
signup_form_data.Name

These query-clauses probably fail miserably when handed to a sql
parser, but the idea is clear: serialized arrays are expedient for PHP
programmers, but they are database unfriendly.

And I'm not calling you out on this, Morbus - you gave a perfect and
helpful explanation for the question.  I just want to point this out
as a sort of motivation to everyone to strive to use better data
storage than serialize($foo).

Regards,
Greg

-- 
Greg Knaddison | Growing Venture Solutions
Denver, CO | http://growingventuresolutions.com
Technology Solutions for Communities, Individuals, and Small Businesses