SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many-to-many schema advice

    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?

  2. #2
    SitePoint Member
    Join Date
    May 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code Ruby:
    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

  3. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,161
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    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

  4. #4
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code Ruby:
    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

  6. #6
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Member
    Join Date
    May 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code Ruby:
    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

    Code Ruby:
    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.

  8. #8
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad you have it working but just to clarify what I was saying, this *isn't* it:

    Quote Originally Posted by sidewaysmilk View Post
    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:

    Code:
    Aaron: "Is that you, Nick?"
    Justin and Nick: "This is UNIX! I know this!"
    and the "participants" field would contain:

    Code:
    Aaron, Justin and Nick
    or a variation of that. But anyway, I'm glad you have it working the way you want it.

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,813
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •