Join Tables

I just have a few questions about join tables. From my understanding
the following applies (correct me if I'm wrong):

1.Join tables are always named with the names of the two associated
tables, in alphabetical order, separated by an underscore.

2.The foreign key fields are named with the name of the table they are
referencing, with _id appended.

3.The foreign key is referencing a single element in that table, so it
uses the singular name.

Correct.

However, I've installed both of the following plugins:

redhillonrails_core
foreign_key_migrations

OK. That will automatically generate foreign key constraints when you
have a *_id field in your migrations.

So, as an example, let's say I created 32 tables with the following type
of information:

def self.up
create_table :rushing_offenses do |t|
t.integer :team_id

[...]

  t\.timestamps
end

end

And I want to create a join table for the team_id to associate with all
those tables:

How would I define the self.up for the join table? Is the naming
convention necessary per above?

No join table is necessary here, since you're not doing a many-to-many
association. As long as the cardinality is 1 on at least one end of
the association, simple keys will do. Of course, you'll want to put
has_many and belongs_to in the appropriate parts of your model code --
see the Association docs for information.

The fact that you're asking this question suggests that you need to
read a lot more about relational database design. In particular, find
out about normalization and especially the Third Normal Form (which is
about as far as most people care to normalize without a good reason).

Best,

Thanks Marnen,

Yeah I'm very weak with database design. I understand how to select
data and display it, and even perform some advanced usages, but I'm weak
on table joins and associations..

I'll look into relational database design. Can you suggest a good book
to read? Or a good article perhaps?

Not easily. My knowledge is more based on tricks I've accumulated
over the years. But a number of the Wikipedia articles seem to be
useful.

I can google hack normalization and third normal form..

Yeah, you'll probably get a lot of hits. Or look at how the
associated models in the Infamous Blog Example are set up.

A lot of times I see exactly what I want to do but getting there is a
hurdle. However, if you saw how much I've accomplished already with my
site, given my knowledge of ruby and rails, I'm certain you'd agree that
I've accomplished a great deal so far (thanks to a lot of help I've
received on these boards from people like you).

Quite likely. I'll have to take a look.

I'll just be patient and do some more reading..

:slight_smile:

Best,

Älphä Blüë wrote:

I'll look into relational database design. Can you suggest a good book
to read? Or a good article perhaps?

I strongly, strongly recommend Enterprise Rails by Dan Chak. The basic
philosophy behind it is that for a successful site, your data (and
therefore your database) will outlive the Rails application on top of
it. So a great deal of discussion (about half the book) is given over to
creating a solid and resilient data foundation, and then getting Rails
to interact with it in a seamless manner. I'm convinced the advice in
this book has saved me from myself many times with Rails.

You're probably using MySQL, and the book covers Postgres, but it sounds
like you're still early enough that switching over won't be much of a
hassle. And it'll really pay off later.

Anyway, a good chunk of the book is on Google Books, so feel free to
page through it a bit.

http://books.google.com/books?id=thTju-4duY4C&pg=PP1&dq=enterprise+rails&ei=8gBDSrrsEoLGlQTQnJX8Dg

Thanks a lot Chris. I appreciate the link and will definitely read up
on it.