# Has\_and\_belongs\_to\_many question **URL:** \ **Category:** Rails\ **Created:** [May 23, 2006, 11:12pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178 "2006-05-23T23:12:48Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 23, 2006, 11:12pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/1 "2006-05-23T23:12:48Z")
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? ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 23, 2006, 11:30pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/2 "2006-05-23T23:30:09Z")
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 ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 23, 2006, 11:42pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/3 "2006-05-23T23:42:30Z")
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](http://www.akropolix.net/rik0/blogs) | site: [http://www.akropolix.net/rik0/](http://www.akropolix.net/rik0/) | forum: [http://www.akropolix.net/forum/](http://www.akropolix.net/forum/) | ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 24, 2006, 12:07am UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/4 "2006-05-24T00:07:37Z")
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 ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 23, 2006, 11:47pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/5 "2006-05-23T23:47:24Z")
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 ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 24, 2006, 1:03am UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/6 "2006-05-24T01:03:39Z")
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. ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 26, 2006, 6:27pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/7 "2006-05-26T18:27:36Z")
On 5/23/06, Brian H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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. ---
**Author:** ![natec](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ec9cab/32.png) [@natec](https://www.ruby-forum.com/u/natec)\ **Post date:** [May 26, 2006, 6:30pm UTC](https://www.ruby-forum.com/t/has-and-belongs-to-many-question/95178/8 "2006-05-26T18:30:57Z")
On 5/26/06, Mathias W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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. 😉 Mathias.