Many-to-many Dance-off!

— April 20, 2006 at 15:53 PDT


I've noticed there's a bit of confusion about the differences between the two ways to create many-to-many relationships using Rails associations. That confusion is understandable, since has_many :through is still pretty new there isn't much written about it. has_and_belongs_to_many is the old, established player and most stuff out there assumes that's what you use for a many-to-many relationship. In fact, a lot of people don't seem to grasp that there is a difference at all!

As we all learned from watching classic movies, the best way to tell the difference between two prospective choices is to have a dance-off. You get to see everyone's moves (which of course are an accurate reflection of inner character), nobody has to die, and the hummable tune makes it a shoe-in for an Oscar nomination. Well, it's either that or do one of those boring "compare and contrast" essays they taught us about in sixth grade English class.

So who are the players we have to choose between? Let's take a quick look at them before the music starts and we get to see their moves.

Join Table: Simple Associations

Table:

create_table "dancers_movies", :id => false do |t|
  t.column "dancer_id", :integer, :null => false
  t.column "movie_id",  :integer, :null => false
end

Models:

class Dancer < ActiveRecord::Base
  has_and_belongs_to_many :movies
end

class Movie < ActiveRecord::Base
  has_and_belongs_to_many :dancers
end

has_and_belongs_to_many associations are simple to set up. The join table has only foreign keys for the models being joined - no primary key or other attributes. (Other attributes were supported using push_with_attributes for a while, but that feature has been deprecated.) There is no model class for the join table.

Join Model: Rich Associations

Table:

create_table "appearances", do |t|
  t.column "dancer_id",      :integer, :null => false
  t.column "movie_id",       :integer, :null => false
  t.column "character_name", :string
  t.column "dance_numbers",  :integer
end

Models:

class Appearance < ActiveRecord::Base
  belongs_to :dancer
  belongs_to :movie
end

class Dancer < ActiveRecord::Base
  has_many :appearances, :dependent => true
  has_many :movies, :through => :appearances
end

class Movie < ActiveRecord::Base
  has_many :appearances, :dependent => true
  has_many :dancers, :through => :appearances
end

has_many :through associations are pretty easy to set up for the simple case, but can get tricky when using other features like polymorphism. The table for the join model has a primary key and can contain attributes just like any other model.

Checking out the moves

Here's the basic feature comparison of the two options.

Association has_and_belongs_to_many has_many :through
AKA habtm through association
Structure Join Table Join Model
Primary Key no yes
Rich Association no yes
Proxy Collection yes no
Distinct Selection yes no yes
Self-Referential yes yes
Eager Loading yes yes
Polymorphism no yes
N-way Joins no yes

There's a lot of good stuff packed into that table, so let's break it down and see what it's all about.

Structure

has_and_belongs_to_many uses a simple join table where each row is just two foreign keys. There's no model class for the join as the join table records are never accessed directly.

has_many :through upgrades the join table to a full-fledged model. It uses a model class to represent entries in the table.

Primary Key

Join tables have no primary key. I've heard some people like to create a primary key from the pair of foreign keys, but Rails won't use that primary key for anything. I'm not sure what you'd get from creating that key, though it might give you a performance benefit depending on your database. (I'm not a DBA so I have nothing more to say about that.)

Join models have primary keys, just like every other model. This means you can access and manipulate records directly.

Rich Association

Way back before Rails 1.1, you could use push_with_attributes to store extra attributes in your habtm join table. There were all sorts of problems with doing that, including not being able to update the attributes later on. push_with_attributes has now been deprecated. If you want a rich association with extra attributes, use a join model.

Proxy Collection

One of the advantages of using habtm is that associations are proxy collections. That means you can create entries in the join table using the association's << method, just like with has_many associations. Since join model records have those extra attributes, it is more complicated to create them automatically the same way join table entries can be. Rails punts on this, so you have to create the join model entries manually. (For a full explanation, see my Why aren't join models proxy collections? article.)

Distinct Selection

Sometimes join table (or model) can have multiple references between the same records. For example, a person may contribute to a book as a writer and an illustrator. If you have multiple references, the database will happily return you all those multiple copies in response to your query. The option :uniq tells the association to filter out duplicate objects so you only get a single copy of each. This is similar to using the DISTINCT keyword in SQL, though the removal of duplicates happens in Ruby instead of the database. When this article was first written only habtm supported :uniq, but now through associations do as well.

Self-Referential

Both habtm and through associations can be self-referential. Users being friends with users is an example of a self-referential relationship. You can do that with habtm using the :foreign_key and :association_foreign_key options on the association. You can do the same thing with through associations, though it's obscure how to do it so I'll have to write up how to manage it soon.

Eager Loading

Both habtm and through associations support eager loading of associated objects with the :include option.

Polymorphism

Join models and through associations can work with polymorphic model types. At least in one direction they do. (c.f. The other side of polymorphic :through associations)

N-way Joins

A habtm association can only join two models. But sometimes you need to represent an association of multiple models. For example, a booking might represent a flight, a passenger, and a seat assignment. Using a through association, you can create a join model that joins as many models as you need. The tricky part is building the queries to get at the associated objects conveniently.

And the winner is...

has_and_belongs_to_many is light on his feet and has some smooth moves. But has_many :through is versatile, even if he has to work harder and his moves are a bit rough in places.

Seriously, there's no way to pick a winner here. Like any engineering decision, choosing a join table or a join model is a matter of picking the right tool for the job (or the right dancer for the part). Now that you've seen our players go head to head, you can make a better choice about who should get that part.

45 commentsassociations, rails

Comments
  1. Ted2006-04-20 17:11:21

    Quality stuff, as always. Keep it up.

  2. Sam Ghods2006-04-20 19:16:17

    First of all, fantastic write up; very much appreciated.

    One thing though: is self-referential has_many :through still buggy? This ticket seems to cover it (http://dev.rubyonrails.org/ticket/4289), and was closed a month ago...?

    I only ask because I'm knee deep in a project right now that could really use it. Please let us know. Thanks!

  3. Josh Susser2006-04-20 19:23:48

    @Sam: Thanks, glad you liked it. Self-ref through associations will work in one direction, but not the reverse direction. The ticket I wrote up on it is still open (http://dev.rubyonrails.org/ticket/4726) and has details on what the problem is.

  4. David2006-04-20 21:53:32

    About the distinct select - I believe you can append :select 'DISTINCT *' to the association, like so: has_many foos, :through => bars, :select 'DISTINCT *'

  5. Josh Susser2006-04-20 22:08:58

    @David: Yes, using :select => 'DISTINCT foo.*' works, but IMO it's just not very DRY (or safe) in that you have to explicitly state the table name. There is a :distinctoption that only works for count()(which I find a bit odd that it's not the same as :uniq). I've been looking into extending :distinct support to find() as well, but I haven't geared up to taking that on yet. Maybe someone who knows the AR code better than I can tackle it :-)

  6. Michael2006-04-21 07:15:16

    Excellent writeup Josh. Thanks.

  7. Brian2006-04-21 07:19:28

    Great article! I wonder about this :distinct business and how it would apply to a rich join model. If you're just interested in getting the distinct records based only on the primary keys, that's one thing. But if you have a rich association, it seems like you'd often want to distinguish the records based on some element of its model, especially if you're throwing polymorphism into it. It would be great if there were a way to say "give me all of this artist's works where the role is 'producer'" in Rails. (Assuming that 'work' is a polymorphic association.)

    Of course there may be exactly that, and I'm just not aware of it.

  8. Godi2006-04-21 10:41:26

    From reading you're (excellent and very thoroughly researched) posts on has_and_belongs_to_many and has_many :through, i can't help but conclude that habtm is going to follow the faith of his bastard son push_with_attributes and is on it's way out.

    It seems to me there is nothing preventing has_many from getting the last push and having the << and :uniq implemented (properly), alltough the question raised by Brian is an interesting one. IMHO this would be preferable to the introduction of an new method name for has_many :through, as suggested elsewhere. Then we would end up with 3 different has_many-kind-off methods.

  9. rick2006-04-21 12:11:31

    @Godi: push_with_attributes is deprecated as far as I know, or slated to get the ax.

    However, has_many :through will not be getting << support. Use the join model to add new records. Calling something like @magazine.subscribers << @user, :started_at => @subscription_start_date doesn't make it obvious that these extra attributes are being added to the @magazine.subscriptions join association. Also, I think it's invalid ruby syntax, forcing you to do something like this: @magazine.subscribers.<<(@user, :foo => 'bar').

    This is much clearer: @magazine.subscriptions << @sub or @magazine.subscriptions.create(:user => @user, :extra => attr)

    I'll probably add an exception so people don't get the idea that << is allowed in has_many :through.

  10. Steven A Bristol2006-04-24 07:35:02

    You said that the join model has a primary key, but you did not put on in the create_table, was this an oversight or is there something special going on?

  11. Josh Susser2006-04-24 08:05:38

    @Steven: the migration method create_table by default adds a primary key named id. The dancers_movies table overrides that with the :id => false options, but the appearances table does not so it gets the primary key. Sorry, that was probably worth a couple sentences to avoid that confusion.

  12. Josh Susser2006-04-24 08:10:13

    @Brian: Yep, you're right. Association extensions are very good for doing those sorts of customized finds, and so are specialized associations.

  13. pic2006-04-30 15:51:25

    interesting article, many thanks.

    A ruby/rails newbie question:

    da = Dancer.find(:first)
    mos = da.movies # give me the d's movies
    mo = mos.first
    

    is there a way to get the character_name of da in mo without falling back in appearances:

    ap = da.appearances.select{| a | a.movie== mo}.first
    puts ap.character_name
    

    In other words is there something that returns ap from da and mo exploiting the :through settings? For example:

    ap = da.appearances_in(mo).first
    
  14. Michel Dogger2006-05-05 12:00:45

    Josh, i've read this blog and it was very usefull to me. Now i've run into the following problem, maybe you have an answer for me.

    I've created the following models:

    model: Account (= table: accounts, with id as PK)

      has_many :AccountVarieties, :dependent => true      
      has_many :varieties, :through => :AccountVarieties
    

    model: Variety (= table: varieties, with id as PK)

      has_many :AccountVarieties, :dependent => true
      has_many :accounts, :through => :AccountVarieties
    

    and my join-model: AccountVariety (= table: accountvarieties, with accountid and variety_id as PK)

      belongs_to :account  
      belongs_to :variety
    

    now i've created the method: delete_variety with this method i want to delete a record in the join table account_varieties.

    This is the method:

      def delete_variety
        account = Account.find(foo)
        account.variety.delete(params[:id])
        redirect_to :action => 'edit', :id => foo
      end
    

    I keep getting the following error: NoMethodError in AccountController#delete_variety undefined method 'variety' for #

    Can you tell me what i am doing wrong?

  15. Josh Susser2006-05-05 12:57:27

    @Michael: First off, don't use the camel-case class name for AccountVarieties in the has_many - use the :account_varieties form instead. That said, you're getting that error becuase there is no #variety method in the Account model. I'm not sure what exactly you're trying to accomplish, so I can't say for sure how to fix it. If you have the id of the AccountVariety, just use the class method delete to get rid of it.

    Probably what you want is to find then delete the AccountVariety object for some combination of :account and :variety ids. You can do something like account.account_varieties.find_first_by_variety_id(id).destroy. You can also use an association extension to help find the AccountVariety by an attribute of the variety if you need to.

  16. Michel2006-05-05 13:19:12

    Thnx Josh,

    My thought was that I could remove the join in account_varieties by saying:

    account.variety.delete(params[:id])

    I've seen a comment from you on the following page: http://www.freeonrails.com/node/2849

    Here you said:

    If you want to remove an entry in the join table, do something like some_user.roles.delete(some_role)

    But if I do understand you right, the best way is to remove the record directy from the join table?

  17. Josh Susser2006-05-05 13:37:48

    @Michel: #variety refers to a SINGLE object, but you've defined a has_many association, not a has_one. This is a duh-level mistake. Also, join models don't work the same way as habtm, so you can't delete the record using the #delete message.

  18. Eden2006-05-09 17:51:37

    Hi Josh,

    Thank you for the write-up. What do you think of the common case where you have a many to many relationship, but one of the join entries should be considered primary? Looking at the Rails mailing list, this seems to be a common situation. Assuming you have no additional information to store, would you suggest using a full join model or just doing a standard habtm and adding an extra FK to your model?

    For example:

    suppliers habtm manufacturers

    You could setup a join model called Representation that contains an attribute called is_primary.

    Or you could just use a habtm relationship and add primary_supplier_id to the Manufacturer model.

    What do you think? I think putting the primary_supplier_id in the Manufacturer model is simpler, but the join table may offer more options in the long term if your relationship grows more complex.

  19. Richard P2006-05-11 19:49:09

    Josh, After much searching, I haven't seen a practical non-trivial example of how to create/update/delete records in a rich join model along with attributes of one side of the association.

    For instance, say you have an administrative interface that allows you to associate dancers with movies they have appeared in and to indicate their character name and dance moves from each movie. The view displays the current dancers real name, associated attributes and a checkbox list of all possible dance movies. When a movie is checked two textboxes appear that allow you to insert the character name and dance moves for the current dancer for that movie.

    So, in the controller update method, how would you update the dancers and appearances tables based on associated attributes and movies that had been checked or unchecked? From my experience, it doesn't appear that you can update the join table through the has_many :through relationship. Perhaps it is just a matter of needing to know how to properly update two related models in one method?

    If you could provide a pointer or an example of this, I would be extremely appreciative and I think that others who have struggled with the question of how best to create/update/delete records in a has_many :through association would also benefit.

    Thank you. Richard

  20. Richard P2006-05-11 20:04:18

    Josh,

    I adjusted this slightly to make it more clear, sorry for the accidental double post...

    After much searching, I haven't seen a practical non-trivial example of how to create/update/delete records in a rich join model along with attributes of one side of the association.

    For instance, say you have an administrative interface that allows you to associate dancers with movies they have appeared in and to input their character name from each movie. The view displays the current dancer's real name, associated attributes and a checkbox list of all possible dance movies. When a movie is checked, a textbox appears that allows you to insert the character name for that movie.

    So, in the controller update method, how would you update the dancers and appearances tables based on associated attributes and movies that had been checked or unchecked? From my experience, it doesn't appear that you can update the join table through the has_many :through relationship. Perhaps it is just a matter of needing to know how to properly update two related models in one method?

    If you could provide a pointer or an example of this, I would be extremely appreciative and I think that others who have struggled with the question of how best to create/update/delete records in a has_many :through association would also benefit.

    Thank you.

  21. lostinnubyland2006-05-15 18:43:26

    I'm with Richard P. I've set up a project with the tables and models and relationships you've shown, but have yet to figure out how this helps me in the CRUD realm.

    For instance: how do you make a new Appearance, where the Dancer and the Movie aren't already in the db?

    How do you make a new Appearance where one or both *is* in the db already?

    And is 'making a new Appearance' the right way to get this data in there? Or is there a way to use (for instance) the Dancer controller in such a way that it asks for the Movie too, and takes care of the Appearance along the way?

  22. lostinnubyland2006-05-15 19:07:23

    Hrmmm, I think I mispelled "Thank you for the informative articles. I still have a couple of questions, if you've got time to address them."

  23. Josh Susser2006-05-17 15:13:18

    @lostinnubyland: Making a new Appearance where you don't already have a Dancer and Movie doesn't make much sense to me. Who is appearing in what? How can you create a row in a join table if you don't have two things to join?

    I can see how one might want to automagically create the appearance object somehow by adding a dancer to a movie, but there are limited cases where that would be useful. For now, I'd write methods in the model classes so you can do movie.new_appearance_of(dancer, name, numbers) or dancer.new_appearance_in(movie, name, numbers).

    As for making a new Appearance, that's pretty standard:

    app = Appearance.new(:movie => movie, :dancer => dancer,
        :character_name => name, :dance_numbers => numbers)
    
  24. Tim Lucas2006-05-28 15:15:05

    @pic: Have you tried using the collection's find method?

    dancer.appearances.find(:first,
                            :conditions => ['movie_id = ?', mo.id]).character_name
    

    unless that doesn't work with :through collections...

  25. Joe2006-06-04 13:14:46

    Great, much-needed article. It clears it up for me, I think ;).

  26. David Andersen2006-06-06 15:56:42

    To me, the clear winner is has_many :through, because one has to jump through major hoops to achieve basic delete functionality of ones join table without having a primary key. I ended up making a join table named as an instance of the connection

    so, actor, actormovieinstance, movie

    with columns: id, actorid, movieid

    class Actor < ActiveRecord::Base
    
      has_many :actor_movie_instances, :foreign_key => "actor_id"
      has_many :movies, :through => :actor_movie_instances
    
    end
    
    class Movie < ActiveRecord::Base
    
      has_many :actor_movie_instances, :foreign_key => "movie_id"
      has_many :actors, :through => :actor_movie_instances
    
    end
    
    class ActorMovieInstance < ActiveRecord::Base
    
      belongs_to :actor, :foreign_key => "actor_id"
      belongs_to :movie, :foreign_key => "movie_id"
    
    end
    
    
    

    that'll provide easy @actor.movies and @movie.actors functionality

  27. Tom2006-07-18 15:37:37

    By the way, this use of dependent was deprecated a little bit ago. From the docs: "NOTE: :dependent => true is deprecated and has been replaced with :dependent => :destroy." Just a note to people who might come here for reference. :)

  28. Michael_SEOG2006-07-21 12:55:06

    Hi Josh Just wanted to thank you for the clear explanations on your blog. I am relatively new to Rails and came searching after watching DHH's presentation on CRUD where he is explaining has many through and figured a blog named "hasmanythrough" would be the place to find out more information -- and I was right.

    The concrete examples and breakdowns are appreciated!

  29. Federico Fernandez2006-08-03 13:10:38

    I wonder if you can have more than one has_many :through association between 2 models.

    For example...

    I have a model Teacher and a model Class

    Now, 1 Teacher works in many Classes, right?. So I need a join model like

    class Work < ActiveRecord::Base

    belongs_to :teacher belongs_to :class

    end

    But I also would like to know if a teacher CAN teach a class before I asign him or her to one class. So I would like to have a new join model like "CanTeach" :

    class CanTeach< ActiveRecord::Base

    belongs_to :teacher belongs_to :class

    end

    The question is... can I do that? Because now the Teacher and Class Models would look like...

    class Teacher< ActiveRecord::Base

    has_many :works has_many :classes, :through => :works

    has_many :works hasmany :classes, :through => :canteach

    end

    and the same for the Class model.

    but that doesn't work..

    Do you know how to do this?

  30. Josh Susser2006-08-03 15:04:48

    @Federico: sure you can do that. You just have to name the associations something different, like has_many :classes vs has_many :classes_can_teach. You'll have to use a :class_name option to specify the class, but the rest should work fine. Alternately, you could have a single join model with an attribute that indicates the state of the relationship, such as can-teach vs does-teach.

  31. Mark P2006-08-10 07:04:43

    @pic, @Tim Lucas: Use da.attributes["character_name"] to avoid Tim's database query every time.

  32. Mark P2006-08-10 15:37:38

    @josh said: "I can see how one might want to automagically create the appearance object somehow by adding a dancer to a movie, but there are limited cases where that would be useful."

    Nonsense.

    In fact, your entire "Why aren't join models proxy collections?" article is an apology for has_many :through associations failure to do just this very thing.

    The habtm implementation was plenty buggy and so is the hasmany :through implementation, but clearly the Rails developers see that hasmany :through has a brighter future. Unfortunately, that future isn't now.

    So, pushwithattributes had bugs, but it did have the virtue of at least attempting to update the memory model (so that pushing a new association would immediately increment the count of associated objects by one) while also updating the database model. pushwithattributes also allowed you to refer directly to da.charactername like @pic wants to do rather than resorting to the attributes hash (which I assume was overlooked due to a rush to push roll out the hasmany :through association).

    The has_many :through association makes no attempt to update the memory model through the "<<" or any sort of "push" operation, and in your "Why aren't join models..." article you give an example of using "<<" and it's failure to update the database model, and an example of creating a new object for the association in the database and its failure to update the memory model.

    Now, it would be trivial to add a method to do both, use "<<" to update the memory model and to stash a new record in the database, but my money is on "<<" being fixed in a later Rails release to do both of the above, so if I do add that new method to my code, I'll have to remember to remove it later when upgrading to a new Rails release that breaks my implementation.

    Polymorphism? Complicated? An excuse for an imcomplete has_many :through implementation? Rails has already standardized on the "type" column for supporting polymorphism. Is it that difficult?

  33. Leevi Graham2006-08-17 01:50:46

    I have a problem I cannot figure out involving polymorphic associations.

    I wish to group users and applications to form groups of users (a user will belong to a goup) eg. authors and groups of applications (an application will belong to a group) eg. blogs.

    I think I have set up my associations correctly and now I wish to add groups of users to any application (an application may have many groups of users).

    I currently can group applications and users with other applications and users like so:

    create_table :users do |t|
    end
    
    create_table :applications do |t|
        t.column :type,           :string
    end
    
    create_table :memberships do |t|
        t.column :group_id,       :integer
        t.column :groupable_id,   :integer
        t.column :groupable_type, :string
    end
    
    create_table :groups do |t|
    end
    
    class User < ActiveRecord::Base
        has_many :memberships, :as => :groupable, :dependent => :destroy
        has_many :groups, :through => :memberships
    end
    
    class Application < ActiveRecord::Base
        has_many :memberships, :as => :groupable, :dependent => :destroy
        has_many :groups, :through => :memberships
    end
    
    class Membership < ActiveRecord::Base
        belongs_to :group
        belongs_to :groupable, :polymorphic => true
    end
    
    class Group < ActiveRecord::Base
        has_many :memberships
    end
    

    How can I add groups to an application? I dont think i can use
    has_many :groups on the application class..

    Any help would be appreciated..

  34. Avishai2006-10-24 11:29:13

    This is useful. I'm currently using hasandbelongsto_many. My models are Shop and Drink, and I have them joined by a drinksshops table, which obviously has an id, shopid, drinkid fields. Is there a way to delete just the reference between a shop and a drink, not actually deleting the drink? (Which is what shop.drinks.find(id).destroy does)

    Any help much appreciated! :-)

  35. chintushah46@gmail.com2006-11-15 05:13:22

    Hello, Can any body tell me how to insert values in the join table using has_many structure? ie i have to insert value in the favorite table in the following structure:

    my table structure is as below:

    class Friend < ActiveRecord::Base has_many :favorites, :dependent => true has_many :users, :through => :favorites end

    class User < ActiveRecord::Base

    has_many :favorites, :dependent => true
    has_many :friends, :through => :favorites
    

    end

    class Favorite < ActiveRecord::Base

        belongs_to :friends
        belongs_to :users
    

    end

    please give me reply me as soon as possible, thnx

  36. jakub.kahovec@gmail.com2006-11-15 12:47:03

    Hello Josh, getting back to deleting records from join table with has_many :through. If there is not primary key in a join table, is the only way how to delete a record from it directly via join class like following

    
      params[:projects_to_delete].each_key do |key|  
    
      ProjectOwner.delete_all("user_id = #{@user.id} AND project_id =    #{key} ") 
    
      end
    
    

    thank you very much in advance for the answer or any advice

  37. Josh Susser2006-11-19 10:16:34

    @jakub: If your join table doesn't have a primary key, you can't use has_many :through. habtm has support for deleting rows from a join table without a PK - just delete the associated object from the habtm collection and the record in the join table gets deleted.

  38. jakub.kahovec@gmail.com2006-11-19 11:05:26

    Thank you for the answer josh. I was actually thinking about using habtm but the join table has got some additional columns and what I understood from your blog was to use has_many :through in that case and it also seemed to me a bit more flexible than habtm. Looking at your examples it didn't seem to me obvious that a join table should have the primary key in order that has_many :through works properly. So now I'm a bit confused ;-) Shall I use habtm even though the join table contains additional columns or hasmany :thourgh and thus use JoinTableClass.deleteall(..) method for 'disassociation' ?

  39. Bill Davenport2007-01-03 15:29:16

    is there a perfomance hit on an Enterpise level for the HM:T ?

  40. Linkfelhő2007-01-09 20:29:11

    Bill: I think no. Anyway I couln't solve that problem:(

  41. Josh Susser2007-01-10 22:17:03

    Bill: what kind of performance hit are you talking about? Relative to what? Joins are fairly expensive operations in SQL, but it's impossible to talk about things like that in the abstract.

  42. John Bachir2007-01-15 19:01:51
    I've heard some people like to create a primary key from the pair of foreign keys, but Rails won't use that primary key for anything. I'm not sure what you'd get from creating that key, though it might give you a performance benefit depending on your database. (I'm not a DBA so I have nothing more to say about that.)

    If you do not specify a primary key and your table does not have any unique numeric indexes, innodb will create it's own "hidden" surrogate sequential primary key in order to organize the rows on the disk. This organization will (in the case of a join table) have absolutely nothing to do with the semantics of your data and will therefor be an index that is never used, costs overhead on queries, and takes up disk space.

    a join table should have two indexes, each with both columns, and each unique. because of how innodb organizes indexes it actually (unless i am unaware of a very obscure detail) does not matter which one you choose to make the primary key.

    (pseudocode)

    table authorships book author primary key (book, author) unique key (author, book)

    now a select on either book or author [1] is indexed and [2] because the indexes are multi-column, will only require one trip to the disk.

  43. John Bachir2007-01-15 19:03:31

    gah, formatting didn't come through. Let's try the pre tag

    table authorships
      book
      author
      primary key (book, author)
      unique key (author, book)

  44. caman2007-01-30 00:40:29

    Interesting article. thank you. This helps a lot. I have one question: If I have a Model 'parent' and it contains has_many associations to 5 tables. Now once parent object is loaded, all the other has_many associated model also get retrieved which slows down the 'parent' model load. What do you recommend this in these kinds of scenario? Should disabling has_many option is the only way? Can we avoid this? what would be the best way? Thanks

  45. Josh Susser2007-01-30 02:42:54

    caman: Association contents are loaded lazily upon access, not when the base object is loaded. There is no up-front cost unless you are explicitly using eager loading with the :include option when finding the base object.

Sorry, comments for this article are closed.