Join Tables

rubyonrails-talk
1

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,

2

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,

3

Ä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.

4

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