Many-to-many schema advice

[sidewaysmilk]

I'm trying to design a schema for a board where quotes are posted. My primary models here are Quote, Line and Person. Here is a run down of the relationships:

Quote
belongs to many people
has many lines
has many people

Line
belongs to many people
belongs to one quote
has many people

Person
belongs to many lines
has many lines
has many quotes

I've rearranged these in so many ways, but I always run into trouble when I start thinking about foreign keys. I'm almost there if I just use quotes as a join table for people and lines, but a line can logically only have one quote! I think that I can just ignore this and be ok, but I'd really prefer to keep it as correct as possible. Any ideas?

[sidewaysmilk]

This seems to cover everything that I'm trying to do (note I've thrown some more tags in there). If anybody reads this, please tell me if I'm doing something obscenely bad.

class Quote < ActiveRecord::Base
  belongs_to :line
  belongs_to :people
  has_many   :quote_tags
  has_many   :tags, :through => :quote_tags
  has_many   :lines
  has_many   :tags
  has_many   :yays
  has_many   :nays
end
 
class Person < ActiveRecord::Base
  belongs_to :quote
  has_many   :quotes
  has_many   :lines, :through => :quotes
  has_many   :tags, :through => :quotes
end
 
class Line < ActiveRecord::Base
  belongs_to :quote
  has_one    :quote
  has_many   :people, :through => :quote
end
 
class Tag < ActiveRecord::Base
  has_many :quote_tags
  has_many :quotes, :through => :quote_tags
end
 
class QuoteTag < ActiveRecord::Base
  belongs_to :quote
  belongs_to :tag
end
 
class Yay < ActiveRecord::Base
  belongs_to :quote
end
 
class Nay < ActiveRecord::Base
  belongs_to :quote
end

[Mittineague]

Hi sidewaysmilk, welcome to the forums,

I'm still pretty much a RoR and DB newbie so I apologize if I'm off mark, but one thing that looks odd is the bi-directional belongs_to-s eg.
Quote belongs_to :line
Line belongs_to :quote
Line has_one :quote

As belongs_to involves foreign keys, should the belongs_to and has_one be only a has_one instead?
http://api.rubyonrails.org/classes/A...s.html#M001791

[Arlen]

Pardon me, but especially where models are concerned, I think the best place to begin is by questioning assumptions.

What are "Lines"? Isn't the quote the atomic particle here?

I'm assuming you're talking about "who said it" and not "who posted it" with person, so wouldn't it be

Quotes
belongs to person

Person
has many quotes

The whole idea of "line" in this context has me stumped. Why is it there?

[sidewaysmilk]

To address the previous question of what a Line is, I'll show an example Quote. Note that the only piece of data in a Line is the words spoken (between "), and the only piece of data in a Person is the person's name. This example does not feature tags.

Allen: "Is that you, Nick?"
Justin and Nick: "This is UNIX, I know this!"

So a "quote" is often an exchange between multiple parties consisting of multiple lines.

I want to be able to retrieve

• Arbitrary quotes
• All quotes in which a given person appears
• All tags for a given person
• All tags for a given quote
• All quotes for a given tag

It looks like the following will work. Thoughts?

class Quote < ActiveRecord::Base
  has_many :lines
  has_many :taggings
  has_many :tags,   :through => :taggings
  has_many :people, :through => :lines,   :source => :says
  has_many :yays
  has_many :nays
end
 
class Line < ActiveRecord::Base
  belongs_to :quote
  has_many   :says
  has_many   :people, :through => :says
end
 
class Person < ActiveRecord::Base
  has_many :says
  has_many :lines,  :through => :says
  has_many :quotes, :through => :lines,  :source => :says
  has_many :tags,   :through => :quotes, :source => :taggings
end
 
class Tag < ActiveRecord::Base
  has_many :taggings
  has_many :quotes, :through => :taggings
  has_many :people, :through => :quotes, :source => :taggings
end
 
class Tagging < ActiveRecord::Base
  belongs_to :quote
  belongs_to :tag
end
 
class Say < ActiveRecord::Base
  belongs_to :person
  belongs_to :line
end
 
class Yay < ActiveRecord::Base
  belongs_to :quote
end
 
class Nay < ActiveRecord::Base
  belongs_to :quote
end

[Arlen]

I guess I still don't see why "lines" exists in this. You're not wanting to retrieve individual lines, only entire quotes. You are trying to retrieve individuals involved in the exchanges, so my first thought would be to create code that parses the quote to identify the speakers and pull that out as a separate field in the quotes model. It seems likely you're going to need something like that for data input anyway.

[sidewaysmilk]

In this context, we may be quoting a conversation. You don't necessarily have the same people contributing to each line in the exchange. If it's a dialog, I need to know who said which line, otherwise in my previous example I'd have

Aaron, Justin and Nick: "Is that you, Nick?"
Aaron, Justin and Nick: "This is UNIX! I know this!"

instead of the correct

Aaron: "Is that you, Nick?"
Justin and Nick: "This is UNIX! I know this!"

The Line is necessary. You may not agree with my choice of names for Quote and Line, but I am positive that these 3 fundamental pieces, Quote, Line and Person, are what I'm looking for.

In any case, I have found my schema. As is usually the case, when the solution occurred to me it seemed so obvious. I was making this WAY too hard.

My new diagram:

sidewaysmilk.com/railroad_models.png

My new models:

class Appearance < ActiveRecord::Base
  belongs_to :person
  belongs_to :line
end
 
class Line < ActiveRecord::Base
  validates_presence_of :body
  belongs_to :quote
  has_many :appearances
  has_many :people, :through => :appearances
end
 
class Nay < ActiveRecord::Base
  belongs_to :quote, :counter_cache => true
end
 
class Person < ActiveRecord::Base
  validates_presence_of :name
  has_many :appearances
  has_many :lines, :through => :appearances
end
 
class  Quote < ActiveRecord::Base
  has_many :lines
  has_many :taggings
  has_many :tags, :through => :taggings
  has_many :yays
  has_many :nays
end
 
class Tagging < ActiveRecord::Base
  belongs_to :quote
  belongs_to :tag
end
 
class Tag < ActiveRecord::Base
  validates_presence_of :name
  has_many :taggings
  has_many :quotes, :through => :taggings
end
 
class Yay < ActiveRecord::Base
  belongs_to :quote, :counter_cache => true
end

And of course I can access all of the data that I wanted without cluttering my schema. If I want all of the quotes and tags that apply to a person, I can just add methods to my model, say

class Person < ActiveRecord::Base
  validates_presence_of :name
  has_many :appearances
  has_many :lines, :through => :appearances
 
  def quotes
    quotes = []
    lines.each do |l|
      quotes << l.quote
    end
    quotes
  end
 
  def tags
    tags = []
    quotes.each do |q|
      tags << q.tags
    end
    tags
  end
 
end

Thanks to everyone who responded. I think I'm all set now.

[Arlen]

Glad you have it working but just to clarify what I was saying, this *isn't* it:

In this context, we may be quoting a conversation. You don't necessarily have the same people contributing to each line in the exchange. If it's a dialog, I need to know who said which line, otherwise in my previous example I'd have

Aaron, Justin and Nick: "Is that you, Nick?"
Aaron, Justin and Nick: "This is UNIX! I know this!"

instead of the correct

Aaron: "Is that you, Nick?"
Justin and Nick: "This is UNIX! I know this!"

What I was saying was quotes table would contain "quote" and "participants" fields (for example). From your example, the "quote" field would contain:

Aaron: "Is that you, Nick?"
Justin and Nick: "This is UNIX! I know this!"

and the "participants" field would contain:

Aaron, Justin and Nick

or a variation of that. But anyway, I'm glad you have it working the way you want it.

[felgall]

Perhaps changing quote to conversation and line to quote might be more descriptive and help you to understand what the relationships should be a little easier.