Has_and_belongs_to_many question

Rails
1

Hello,
I am trying to make the “switchover” to programming my apps in Rails and
have a question about database structure.

In the past i would structure a many to many like this:

categories

| id | name |

1 Some Category
2 Another Category
3 One More Category

sub_categories

| id | name | category_id |

1 SubCategory One 1,2
2 SubCategory Two 2
3 SubCategory Three 1,2,3

is there a way for Active Record to handle this type of table linking or
would it be better for me to create the third table to link these
together?

2

Um… Wow. I can honestly say I would have never thought to build a
many-to-many relationship with that kind of a table structure. In
fact, this is the first time I’ve ever seen, or heard, of someone
building a many-to-many relation without the use of a join table.

In Rails, you must use a join table (or a full-blown join model) if
you want to take advantage of the built in relationship management
features. So, if you have a simple many-to-many, between categories
and subcategories (I dropped the extra _ for neatness ;), you would
have:

categories

| id | name |

1       Some Category
2       Another Category

sub_categories

| id | name |

1       SubCategory One
2       SubCategory Two

categories_subcategories

| category_id | subcategory_id |

    1                  1
    1                  2
    2                  1

Notice how the join table is named, how the foreign key fields are
named, and also the fact that the join table has no “id” column of
its own. This is how you structure things so you can have:

class Category < ActiveRecord::Base
has_and_belongs_to_many :subcategories
end

class Subcategory < ActiveRecord::Base
has_and_belongs_to_many :categories
end

-Brian

3

Nate C. wrote:

is there a way for Active Record to handle this type of table linking or
would it be better for me to create the third table to link these
together?

Have you tried to use the “acts_as_tree” specifier?

–
blog: http://www.akropolix.net/rik0/blogs |
site: http://www.akropolix.net/rik0/ |
forum: http://www.akropolix.net/forum/ |

4

Well, I have come across that way of organising many-to-many
relationships and it’s a real hit on performance once stuff get’s
big… although you did say that the relationship is limited, so maybe
it wouldn’t be. But arraying data like that just generally isn’t a
good idea.
-N

5

Brian,
I have used the relationship that you specified in the past but for many
to many relationships that are not unlimited i have used the way i
specified previously. Specifically, in other apps i have used that way
to link code modules to user’s roles like this:

roles

| id | name |

1 Some Role
2 Another Role
3 One More Role

modules

| id | name | roles |

1 Module One 1,2
2 Module Two 2
3 Module Three 1,2,3

I guess it would techinically be better to use the third table in this
situation but because the data in the table is not changing regularly
(or at all) then i tried to make it easier… But that was WAAAYYY BACK
when i was using php! LOL

thanks again for your response; i’ll use the 3 table method so i can use
Active Record like it was intended.

Nate

6

unknown wrote:

Well, I have come across that way of organising many-to-many
relationships and it’s a real hit on performance once stuff get’s
big… although you did say that the relationship is limited, so maybe
it wouldn’t be. But arraying data like that just generally isn’t a
good idea.
-N

Thank you for the advice… until now, i have just created ways that
worked… regardless if it is proper or not.

7

On 5/23/06, Brian H. [email protected] wrote:

Notice how the join table is named, how the foreign key fields are
named, and also the fact that the join table has no “id” column of
its own. This is how you structure things so you can have:

I just ran into some issues which I believe is due to Rails havnig
added that id-column on its own. Is there any proper way to create
them or do I just have to use execute and create it myself in SQL?
Could I perhaps just run a remove_column after I’ve created it?

Thanks,
Mathias.

8

On 5/26/06, Mathias W. [email protected] wrote:

I just ran into some issues which I believe is due to Rails havnig
added that id-column on its own. Is there any proper way to create
them or do I just have to use execute and create it myself in SQL?
Could I perhaps just run a remove_column after I’ve created it?

Thanks,
Mathias.

And as always I stumble upon the solution a few minutes after I posted
it to the mailing list.

create_table :table_name, :id => false do…

That did the trick. Thanks for listening. :wink:

Mathias.