SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
Thread: Many-to-many schema advice
-
May 18, 2009, 16:14 #1
- 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?
-
May 18, 2009, 16:58 #2
- 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
-
May 19, 2009, 23:47 #3
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 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#M001791Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
May 20, 2009, 06:01 #4
- 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?
-
May 20, 2009, 08:52 #5
- 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
-
May 21, 2009, 07:03 #6
- 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.
-
May 21, 2009, 10:22 #7
- 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.
-
May 22, 2009, 08:21 #8
- 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:
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!"
Code:Aaron, Justin and Nick
-
May 22, 2009, 13:23 #9
- Join Date
- Sep 2005
- Location
- Sydney, NSW, Australia
- Posts
- 16,875
- 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