# Storing a Hash in a database table using YAML--anything better? **URL:** \ **Category:** ruby-talk\ **Created:** [19 October 2006 23:51 UTC](https://rubytalk.org/t/storing-a-hash-in-a-database-table-using-yaml-anything-better/32110 "2006-10-19T23:51:47Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![J\_B\_Rainsberger](https://avatars.discourse-cdn.com/v4/letter/j/b5e925/32.png) [@J\_B\_Rainsberger](https://rubytalk.org/u/J_B_Rainsberger)\ **Post date:** [19 October 2006 23:51 UTC](https://rubytalk.org/t/storing-a-hash-in-a-database-table-using-yaml-anything-better/32110/1 "2006-10-19T23:51:47Z")
I'm a beginning Rails programmer, so please be gentle. I want to store a hash as a single row in a database table. I'm currently storing each name/value pair as a row with a reference back to the "parent" row, but that seems unnecessarily complex. I want to know whether you have any better suggestions than what I plan to try to do. I plan to write the hash into the row in a single column by YAMLing it. I would, of course, unYAML on read operations. I do not plan ever to need to run a query on a single key in this hash, so I feel comfortable storing it blob-like this way. I just wanted to know whether anyone knows of a better way than YAML formatting/parsing in my Rails model object. Thanks for your help! > **···** > > -- > J. B. (Joe) Rainsberger :: [http://www.jbrains.ca](http://www.jbrains.ca) > Your guide to software craftsmanship > JUnit Recipes: Practical Methods for Programmer Testing > 2005 Gordon Pask Award for contribution Agile Software Practice ---
**Author:** ![Gregory\_Seidman](https://avatars.discourse-cdn.com/v4/letter/g/eb9ed0/32.png) [@Gregory\_Seidman](https://rubytalk.org/u/Gregory_Seidman)\ **Post date:** [20 October 2006 01:18 UTC](https://rubytalk.org/t/storing-a-hash-in-a-database-table-using-yaml-anything-better/32110/2 "2006-10-20T01:18:18Z")
} I'm a beginning Rails programmer, so please be gentle. } } I want to store a hash as a single row in a database table. I'm } currently storing each name/value pair as a row with a reference back to } the "parent" row, but that seems unnecessarily complex. I want to know } whether you have any better suggestions than what I plan to try to do. } } I plan to write the hash into the row in a single column by YAMLing it. } I would, of course, unYAML on read operations. } } I do not plan ever to need to run a query on a single key in this hash, } so I feel comfortable storing it blob-like this way. I just wanted to } know whether anyone knows of a better way than YAML formatting/parsing } in my Rails model object. Look at ActiveRecord::Base.serialize. It lets you declare a column (make sure it's a TEXT column) to be a serialized Ruby object. You can store your hash automatically. You may also be interested in this example use of it: [http://redcorundum.blogspot.com/2006/07/ror-additional-attributes-with-sti.html](http://redcorundum.blogspot.com/2006/07/ror-additional-attributes-with-sti.html) } Thanks for your help! } J. B. (Joe) Rainsberger :: [http://www.jbrains.ca](http://www.jbrains.ca) --Greg > **···** > > On Fri, Oct 20, 2006 at 08:51:47AM +0900, J. B. Rainsberger wrote: ---
**Author:** ![David\_Vallner](https://avatars.discourse-cdn.com/v4/letter/d/eb9ed0/32.png) [@David\_Vallner](https://rubytalk.org/u/David_Vallner)\ **Post date:** [20 October 2006 10:10 UTC](https://rubytalk.org/t/storing-a-hash-in-a-database-table-using-yaml-anything-better/32110/3 "2006-10-20T10:10:29Z")
J. B. Rainsberger wrote: > I want to store a hash as a single row in a database table. I'm > currently storing each name/value pair as a row with a reference back to > the "parent" row, but that seems unnecessarily complex. I want to know > whether you have any better suggestions than what I plan to try to do. Disclaimer: A lot of the following will be database access performance related. In personal code with low usage, those can be safely ignored, but for a self-education project, I prefer following good practices to learn them even if they seem clunky. What's the structure of the hash? If it could be replaced by a data structure, I'd do so and keep this approach (it IS the correct one from the relational data modelling point of view), unless you're sure the hash is an atomic value object by design. I'd especially not use the serialization approach if you need to filter your data according to the contents of that hash - the "draw all records from the DB and filter them in Ruby" approach makes baby DBA Jesus cry and is a good candidate for becoming a bottleneck. I'd also not use the serialization approach if it's binary serialisation - if you ever had to access the database from anything except Rails (e.g. Crystal Reports), you're screwed. David Vallner ---
**Author:** ![BIl\_Kleb1](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@BIl\_Kleb1](https://rubytalk.org/u/BIl_Kleb1)\ **Post date:** [22 October 2006 22:45 UTC](https://rubytalk.org/t/storing-a-hash-in-a-database-table-using-yaml-anything-better/32110/4 "2006-10-22T22:45:13Z")
J. B. Rainsberger wrote: > I'm a beginning Rails programmer, so please be gentle. Hi JB! Welcome. Regards, > **···** > > -- > Bil Kleb > [http://fun3d.larc.nasa.gov](http://fun3d.larc.nasa.gov) ---
**Author:** ![J\_B\_Rainsberger](https://avatars.discourse-cdn.com/v4/letter/j/b5e925/32.png) [@J\_B\_Rainsberger](https://rubytalk.org/u/J_B_Rainsberger)\ **Post date:** [20 October 2006 04:23 UTC](https://rubytalk.org/t/storing-a-hash-in-a-database-table-using-yaml-anything-better/32110/5 "2006-10-20T04:23:45Z")
Gregory Seidman wrote: > **···** > > > On Fri, Oct 20, 2006 at 08:51:47AM +0900, J. B. Rainsberger wrote: > > } I'm a beginning Rails programmer, so please be gentle. > > } } I want to store a hash as a single row in a database table. I'm } currently storing each name/value pair as a row with a reference back to } the "parent" row, but that seems unnecessarily complex. I want to know } whether you have any better suggestions than what I plan to try to do. > > } } I plan to write the hash into the row in a single column by YAMLing it. } I would, of course, unYAML on read operations. > > } } I do not plan ever to need to run a query on a single key in this hash, } so I feel comfortable storing it blob-like this way. I just wanted to } know whether anyone knows of a better way than YAML formatting/parsing } in my Rails model object. > > > > Look at ActiveRecord::Base.serialize. It lets you declare a column (make > > sure it's a TEXT column) to be a serialized Ruby object. You can store your > > hash automatically. You may also be interested in this example use of it: > > [Ruby, iOS, and Other Development: RoR: Additional Attributes with STI](http://redcorundum.blogspot.com/2006/07/ror-additional-attributes-with-sti.html) > > This is exactly the kind of thing I was hoping to see. Thank you, Greg! I'll let you know how it goes. > -- > J. B. (Joe) Rainsberger :: [http://www.jbrains.ca](http://www.jbrains.ca) > Your guide to software craftsmanship > JUnit Recipes: Practical Methods for Programmer Testing > 2005 Gordon Pask Award for contribution Agile Software Practice