[Boulder-Denver Ruby Group] Rails ActiveRecord Question

[Brian Gibson]

You should consider Polymorphic Associations or Single Table Inheritence to accomplish this.

STI is probably closer to your "one-table" for all "stuff" requirement but if your different kinds of stuff" have vastly different attributes your table of "stuff" will be very wide.  

Polymorphic Associations on the other hand will allow you to create an association from one class to multiple classes.

http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance
http://wiki.rubyonrails.com/rails/pages/PolymorphicAssociations


----- Original Message ----
From: Tim Pease <tim.pease at gmail.com>
To: bdrg-members at rubyforge.org
Sent: Thursday, November 9, 2006 12:40:32 PM
Subject: [Boulder-Denver Ruby Group] Rails ActiveRecord Question

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