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? -- Posted via http://www.ruby-forum.com/.
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
On May 23, 2006, at 05:12 PM, Nate Constant wrote:> 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?
Nate Constant 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/ |
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 -- Posted via http://www.ruby-forum.com/.
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 On 23/05/06, Brian Hughes <brianvh@alum.dartmouth.org> wrote:> > 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 > > > On May 23, 2006, at 05:12 PM, Nate Constant wrote: > > 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? > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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. > -NThank you for the advice.. until now, i have just created ways that worked.. regardless if it is proper or not. -- Posted via http://www.ruby-forum.com/.
On 5/23/06, Brian Hughes <brianvh@alum.dartmouth.org> 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.
On 5/26/06, Mathias Wittlock <wittlock+rubyonrailslist@gmail.com> 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.