Avoiding Duplicate Data in DB (for use with Rails)

Question

I have five tables that I am trying to get to work nicely together but may need some help.

I have three main tables:

• accounts
• members
• and roles.

With two join tables

• account_members
• and account_member_roles.

The accounts and members table are joined by account_members (fk account_id and member_id) table.

The other 2 tables are the problem (roles and account_member_roles).

A member of an account can have more than one role and I have the account_member_roles (fk account_member_id and role_id) table joining the account_members join table and the roles table.

That seems logical but can you have a relationship with a join table? What I'd like to be able to do is when creaeting an account, for instance, I would like @account.save to include the roles and update the account_member_roles table neatly ..... but through the account_members join table.

I've tried .....

accept_nested_attributes_for :members, :account_member_roles

in the account.rb but I get .....

ActiveRecord::HasManyThroughCantAssociateThroughHasManyReflection (Cannot modify association 'Account#account_member_roles' because the source reflection class 'AccountMemberRole' is associated to 'AccountMember' via :has_many.)

upon trying to save a record.

Any advice on how I should approach this?

CIA

-ants

Answer 1

Take a read here: http://blog.hasmanythrough.com/2006/4/17/join-models-not-proxy-collections

The main difference between a simple has_and_belongs_to_many join table and a has_many :through join model is that the join model can have attributes other than the foreign keys for the records it is joining. In fact, if you didn't have those other attributes you probably wouldn't use a join model and would settle for a join table.

So it seems that you want to use has_many :through and you would be set.

Answer 2

If I am reading this correctly you have:

accounts habtm members

members habtm accounts

members has_many roles

If that is accurate you just need a join table between accounts and members and then just a regular FK between members and roles. @account.members and @account.members.roles would both give you access to the attributes you need and @account.save should save the whole mess without a fuss.

I am not sure what compels you to have that account_members_roles table. I don't if Rails can handle that. I know I sure can't. :)