Multiple Keys in a Database Table

I’ve read previous posts to the list stating how Rails lacks multiple
key
support for database tables, and how support is not planned. I’m OK with
that, but I’m wondering if there’s an elegant way to do what I need, for
my
in-house customer relationship management tool:

I have an ACTIVITIES table. That table is a collection of individual,
unique
activities created by users of the system. Activities have activity
types,
listed in the ACTIVITY_TYPES table (and joined by an activity_type_id in
the
activities table, naturally). Right now, there are four activity types,
but
that table will eventually expand. Each activity type (e.g.,
“registration,”
“phone call,” “email,” etc.) has many fields, in the ACTIVITY_FIELDS
table,
associated with it, and those fields propagate up to the individual
activities. These two tables are joined by a has_and_belongs_to_many
association, because certain fields should be available to multiple
activity
types. So available fields (e.g., “phone number,” “email address,” etc.)
are
listed in each activity type, and I can drill down into the list of
available fields by calling @activity.activity_type.activity_fields.

My problem, however, is that the activity fields and the individual
values
per activity for those fields are stored in separate tables. I have my
activity_fields table and also an ACTIVITY_FIELD_VALUES table. This
table
stores the value separately so I can provide a CRUD interface for the
fields
themselves without querying and grouping a key/value table of fields and
their values (if a field never gets set in that table, but should still
exist, that query wouldn’t find it). The way I thought about this
problem
initially was to have the activity_fields table set up like this: id,
name,
description, and the activity_field_values table set up like this: id,
activity_id, activity_field_id, value. Doing that, I’d have unique
activity
field values for each activity and field ID. The problem I ran into
was…
how do I access that value? If I accessed @
activity.activity_type.activity_fields, I could get all the fields
associated with a particular activity type (the keys). But accessing the
values associated with those keys is a mystery to me. Maybe I’m not
thinking
of something easy, or maybe I’m thinking of something only really
solvable
(that way) by multiple table keys.

I’m thinking the solution is to introduce another
has_and_belongs_to_many
table linking the activities table and the activity_field_values table.
It
just doesn’t seem as graceful for some reason, and how would I keep the
keys
and the values consistent when accessing a list? Would it be something
like
@activity.activity_type.activity_field[0] to find the key, and @
activity.activity_field_values[@activity.activity_type.activity_field[0]]
to
find the value?

My head is fried. I’ve been trying to figure this out at work for the
past
hour, and I’m coming up short. ANY help is appreciated. If my schema
design
sucks, let me know. If there’s something I’m totally missing, I’m open!

Thanks,
Adam

What about using single table inheritence and the ‘type’ column?
Create a base Activity and then subclass it?

http://www.juixe.com/techknow/index.php/2006/06/03/rails-single-table-inheritance/

I have a CRM project where I created two superclasses (Entity,
Activity) that everything else in the system derives from. The
Activity classes were things like: SalesActivity, ServiceActivity,
ReportActivity, etc.

On 7/5/06, Adam B. Traver [email protected] wrote:

“registration,” “phone call,” “email,” etc.) has many fields, in the
activity_fields table and also an ACTIVITY_FIELD_VALUES table. This table
fields associated with a particular activity type (the keys). But accessing
@activity.activity_field_values[@activity.activity_type.activity_field[0]]
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails


Michael R. Rutherford
Software Engineer/Business Analyst
615.319.8905
http://agentmine.com/blog

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs