ActiveRecord many to many should I make another table to hold the relation information?

rails associations
rails has_many foreign key
rails g many to many
rails create many-to-many record
rails associations tutorial
rails has_many :through example
rails inverse_of
rails references

I have albums and artists tables that should relate as many to many: each album can be owned by multiple artists and each artist can have multiple album. But I need to know whether the artist is the main artist or just a contributor.

Currently I just use artists column in albums table to hold artists id in semicolon separated strings (format: 3;6;343;56;1). The main artist's id should appear first, the rest is just contributors.

Currently I access artist's contribution in an albums by query with .where and LIKE keywords. Then filter the array result to exclude prefix (artist id), based on this answer.

@albums_ = Album.where("artists LIKE :prefix", prefix: "%#{params[:id]}%")
@albums_contribute_to = { |album| !album.artists.start_with?("#{params[:id]}") }

Is there any more effective way to accomplish this?

Played around with it for a little bit, the big idea was to put some additional information on the join table (in my example, I call it primary). And then read the docs to figure out how to tell ActiveRecord to use them.

# setup active record
require 'active_record'
ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'

# some tables (migrations / schema)
ActiveRecord::Schema.define do
  create_table(:artists) { |t| t.string :name}
  create_table(:albums)  { |t| t.string :name }
  create_table :appearances do |t|
    t.integer :artist_id
    t.integer :album_id
    t.boolean :primary, default: false # <-- join table specifies who is primary

# some models
class Artist < ActiveRecord::Base
  has_many :appearances
  has_many :albums, through: :appearances

class Appearance < ActiveRecord::Base
  belongs_to :artist
  belongs_to :album

class Album < ActiveRecord::Base
  # three associations to appearances
  has_many :all_appearances,                                   class_name: 'Appearance'
  has_many :primary_appearances,  -> { where primary: true  }, class_name: 'Appearance'
  has_many :featured_appearances, -> { where primary: false }, class_name: 'Appearance'

  # three associations to artists
  has_many :all_artists,      through: 'all_appearances',      source: 'artist'
  has_many :primary_artists,  through: 'primary_appearances',  source: 'artist'
  has_many :featured_artists, through: 'featured_appearances', source: 'artist'

# some artists
dre  = Artist.create! name: 'Dr. Dre'
dogg = Artist.create! name: 'Snoop Dogg'
slim = Artist.create! name: 'Eminem'

# some albums
weed = Album.create! name: 'The Chronic 2001',
                     primary_artists:  [dre],
                     featured_artists: [dogg, slim]

show = Album.create! name: 'The Eminem Show',
                     primary_artists:  [slim],
                     featured_artists: [dre]

# it understands the associations
weed.all_artists.pluck :name      # => ["Dr. Dre", "Snoop Dogg", "Eminem"]
weed.primary_artists.pluck :name  # => ["Dr. Dre"]
weed.featured_artists.pluck :name # => ["Snoop Dogg", "Eminem"]

# might be nice to add similar scoped associations to get from Artist to Album
weed               # => #<Album id: 1, name: "The Chronic 2001">
  .primary_artists # => #<ActiveRecord::Associations::CollectionProxy [#<Artist id: 1, name: "Dr. Dre">]>
  .first           # => #<Artist id: 1, name: "Dr. Dre">
  .albums          # => #<ActiveRecord::Associations::CollectionProxy [#<Album id: 1, name: "The Chronic 2001">, #<Album id: 2, name: "The Eminem Show">]>
  .last            # => #<Album id: 2, name: "The Eminem Show">
  .primary_artists # => #<ActiveRecord::Associations::CollectionProxy [#<Artist id: 3, name: "Eminem">]>
  .first           # => #<Artist id: 3, name: "Eminem">

Active Record Associations, With Active Record associations, we can streamline these - and other another, you instruct Rails to maintain Primary Key-Foreign Key information between the table for the class declaring the belongs_to association), but you should give If you don't need to do anything with the relationship model, it may be simpler to  Be aware that has_and_belongs_to_many saves association to join table immediately after assign. It does NOT wait for It does NOT wait for Hence if save does not get through validations (or fail for any other reason), associated records will still be in the database.

It is typical case for using joined table. If you want to create that kind of relationship. What you are doing will only create problems in the long run as you will have to hold that information in memory, which is also slow.

In ruby on rails that relation would like something along this lines:

class Album < ApplicationRecord
  has_many :album_artists
  has_many :artists, through: :album_artists

class AlbumArtist < ApplicationRecord
  belongs_to :artist
  belongs_to :album

class Artist < ApplicationRecord
  has_many :album_artists
  has_many :albums, through: :album_artists

If you would like to avoid doing that with ruby on rails you have to implement custom query but the rules are the same, you have to create some kind of joined table.

Many-To-Many Relationships, In this lesson, you'll learn how to model more complex data structures in Rails. If we want to keep a record of which authors favorited which messages, where would we Instead, many-to-many relationships are created by introducing a third table, INTERACTIVE SESSION: Create a new migration for the favorites table. The 10 Most Underused ActiveRecord::Relation Methods 14 April 2012. Knee-deep in ActiveRecord::Relation code yesterday, I was reminded of some interesting nuggets that I’ve seen used far too rarely. Here, I’ve gathered my top ten most underused relation methods from that list for your reading delight.

Agree with Joshua and Przemek, it's an easy solution. here is how to use it:

class Appearance < ActiveRecord::Base
  belongs_to :artist
  belongs_to :album
  # has attribute "primary"

class Artist < ActiveRecord::Base
  has_many :appearances
  has_many :albums, through: :appearances

class Album < ActiveRecord::Base
  has_many :appearances
  has_many :artists, through: :appearances

# create album with 2 artists
  name: "foo_bar",
  appearances: [ Artist.create(name: "foo"), primary: true), Artist.create(name: "bar"))

# list artists
album = Album.first
album.appearances.order("piramary desc").each do |appearance|
  puts "#{} #{appearance.primary? ? "(Primary)" : ""}"

4. Active Record Relationships, Active Record Relationships You've seen how Active Record treats a model But Photo Share will need many different models working together. Rails will add everything you need to manage each relationship based on a few Each row of a join table expresses a relationship with foreign keys, but has no other data. In Rails, an association is a connection between two Active Record models. Why do we need associations between models? Because they make common operations simpler and easier in your code. For example, consider a simple Rails application that includes a model for authors and a model for books. Each author can have many books.

I really don't think you need a separate model that joins the Album and Artist one. So you can use the has_and_belong_to_many(HABTM) method that is coming from ActiveRecord and is used for transitive many to many relations. You will also need a table that joins the 2 models and you can easily create one with a migration.

I highly recommend you to read this article. It will be really helpful for you.

Pro Active Record: Databases with Ruby and Rails, Doing that would only allow one of the tables to be associated to many records in the other (it would only be a many-to-one relationship). In order to keep the database normalized, a has_and_belongs_to_many (habtm) association we have our associations defined, let's look at another plain English requirement: get a list  So instead of a fast sequential read from your hard-drive, your data-store server has to hop around all over the place to get what it needs. Since ActiveRecord returns everything by default, adding a text field to a table slows everything down drastically, across the board. Not so with the DataMapper.

Master Many-to-Many Associations with ActiveRecord, Fred Heath discusses the many different many-to-many associations and how to handle Modeling many-to-many relationships between data entities in the Then, we can create our database tables by running: no need for us to do anything more and ActiveRecord will model our association perfectly. Inspired by and put together the awesomeness of many yii extensions that aim to improve saving of related records. Comes with 100% test coverage and well structured and clean code so it can savely be used in enterprise production enviroment. - yiiext/activerecord-relation-behavior

Everything There Is to Know About Associations in Rails, Learn how to get started with associations in Rails by following these simple One of the models in the given relation will have has_one method It is a table that connects two different models. This is a very simple approach, but you don'​t have the direct access to related objects, you can only hold  How many rows can a SQL Server 2012 table hold? Of course, older data is used less often and is candidate for being partitioned in multiple tables on the same DB. But when should this partitioning start happening? 1 table per year? 5 years? Additional information collected from comments:

One-to-One Association, In this module, we will continue exploring Active Record and look at ways to Keep your students learning with free access to over 3800 courses from top global universities. diagram, a database is a bunch of tables that are related to each other. Now another point I should mention is, why do we have relationships in a  Active Record Relation. Returns a stable cache key that can be used to identify this query. The cache key is built with a fingerprint of the SQL query.