I Don't Understand How to Express a Relationship Between Three Separate Tables

Question

Given the following tables in ActiveRecord:

• authors
• sites
• articles

I don't know how to express that an author is paid a different amount depending on the publication, but that authors working for the same publication have different rates:

• John publishes an article in Foo for $300
• John publishes an article in Bar for $350
• John publishes an article in Baz for $400
• Dick publishes an article in Foo for $250
• Dick publishes an article in Bar for $400

etc.

What kind of relationship am I trying to describe?

At the moment I've got a "rates" table with author_id, site_id and amount columns. Given publication.id and author.id, I derive the cost of the article with

cost = Rate.find(:first, :conditions => ["author_id = ? and site_id = ?", author.id, site.id]).rate

That works, but I'm not sure it's the best way, and I'm not sure how to make sure I don't end up with 'John' having two rates for 'Baz.'

I don't think I want code so much as I want someone to say "Oh, that's a ... relationship" so I can get a grip on what I'm Googleing for.

Answer 1

Its straightforward, but I don't know if there's a specific name for this relationship.

It looks like you need three tables:

1. Author (info about authors)
2. Site (info about sites)
3. Rate Author/Site (rate info only)

In the third table you'd have at least:

• Author ID (FK to Author, and Primary Key)
• Site ID (FK to Site, and Primary Key)
• Rate

And the rate table has two fields as primary keys with a unique constraint. And any joins involving the author and sites would involve a 3-table join.

Answer 2

I would use a third table:

author_site_mapper
------------------
id
author_id
site_id
rate

I've generally heard this referred to as a 'mapper' relationship. It signifies a many-to-many relationship between two tables.

Answer 3

Its a has and belongs to many with a rich join table.

class Author
  has_many :publications, :through => :rates
end

class Publication
  has_many :authors, :through => :rates
end

class Rate #rich join table
  belongs_to :author
  belongs_to :publication
end

And you can then simplify your finding like this:

@author.rates.find_by_site_id(123)

Plus you get direct access accross the join table

@author.publications
@publication.authors

Answer 4

When three entities are related to each other, it's called a ternary relationship.

Most of the relationships we deal with are binary, relating two entities to each other. For example, The "enrolled in" relationship between students and courses. Binary relationships are further categorized into many-to-many, many-to-one, and one-to-one. But you knew that.

Ternary relationships can be categorized as many-to-many-to-many, many-to-many-to-one, many-to-one-to-one, and so on.

Binary and Ternary relationships can be further generalized to n-ary relationships.

Here's how I see the case you outlined: There are three entities: author, publication, and article. In addition to these three entities, there is a measure, namely rate. I could be wrong about this.

So I would see three entity tables:

Authors with PK AuthorID.
Publications with PK PublicationID.
Articles, with PK ArticleID.

Then there would be a relationship table with four columns:

AuthorID (FK), PublicationID (FK), AtricleID (FK), Rate which is a currency amount.

The PK of this is (AuthorID, PublicationID, ArticleID)

Not that, in this design, there is no rate table. It's just a measure.

Note also that in this design, it's possible for several authors to collaborate on one article, and each be given a separate rate for his/her share of the article. That's not possible in some of the other proposed designs.

It's also possible for the same article to be sold to more than one publication. It might be desirable to impose constraints on the data, if the real world imposes the same constraints.

Anyway, if you want a search term to Google, the term is "ternary relationships".