[Boulder-Denver Ruby Group] Rails ActiveRecord Question

[David Clements]

On 11/9/06, Tim Pease <tim.pease at gmail.com> wrote:
> This is a little long, but please bear with the explanation.
>
> I want to be able to setup a database table that can hold different
> kinds of "stuff" where each type of "stuff" can have vastly different
> contents (or attributes). For example, one type of stuff would be
> information about a person -- name, age, address, hair color, etc.
> Another type of stuff would be a book -- title, author, ISBN.
>
> I would like to store all these things in one table so that I can have
> a join table linking a user to their "stuff". The join table would
> have user specific attributes about the stuff (description, tags,
> etc).  The "stuff" table would only have one copy of each "stuff"
> item.
>
> Some SQL ...
>
> CREATE TABLE stuff (
>     id  INT  NOT NULL  AUTO_INCREMENT PRIMARY KEY,
>     type VARCHAR(50) NOT NULL,
>     name TEXT  NOT NULL,
>     description  TEXT  NULL
> );
>
> CREATE TABLE stuff_attributes (
>     stuff_id  INT NOT NULL,
>     name VARCHAR(45) NOT NULL,
>     value TEXT NOT NULL
> );
>
>
> The idea here is that each "type" of stuff gets stored in the stuff
> table with the type attribute set to what it is ('person' or 'book').
> The attributes about that stuff are then stored in the
> stuff_attributes table, with each attribute being a separate entry
> with the "name" of the attribute and the "value" for the attribute.
>
> For a book, then, there would a single entry in the stuff table
> describing the thing's type as a "book", the book's name and a
> description.  The stuff_attributes table would have three entries --
> the title attribute and its value, the author attribute and its value,
> and the ISBN attribute and its value.
>
> Obviously there will need to be an SQL join when doing any query to
> get information about particular stuff.
>
> Does ActiveRecord make this dead simple?  Or will I end up rewriting
> most of the ActiveRecord methods to make this kind of table schema
> work?
>
> More general, is there a better way of doing this kind of generic data
> about stuff in ActiveRecord?
>
> Any helpful pointers are appreciated. Actual working Rails code with
> attached database schema will get you a free beer on Wednesday night
> ;)
>
> Blessings,
> TwP
> _______________________________________________
> Bdrg-members mailing list
> Bdrg-members at rubyforge.org
> http://rubyforge.org/mailman/listinfo/bdrg-members
>


Hey Tim,

If I am following you correctly I have a solution in production that
we are using.  The domain for my problem was that we kept on adding
new preferences to our Users and Customers.   I didn't want to change
the schema for the Users tables or the Customers table each time we
needed one of these new attributes.   Luckily I have caught Ara's
Metaprogramming Madness and created some code to help me with this
problem.


I created a Preferences table with at name, value and type.   I
employed STI and created a UserPreferences model.

Now in my Users model I am able to declare the preferences that a User
has with something like this:

class User < ActiveRecord::Base
  include HasManyPreferences


has_many_preferences :email_type => "html", :alternate_email_address
=> nil, :sectors => nil

end

What HasManyPreferences does this:

1) Creates the has_many relationship to user_preferences
2) Adds methods for the singular preferences so that you can do
user.email_type = 'plain_text'
3) Adds accessor types methods for the plural prefernces so that you
can user.add_sector("Tech") or user.delete_sector

2 and 3 in turn manage the has many relationship under the hood.

For our use case these are all user driven ops so I don't know how it
would scale.

Let me know if you want to see the HasManyPreferences code.


Dave