Join Tables

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

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

redhillonrails_core
foreign_key_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.string :name
t.integer :games
t.integer :carries
t.integer :net
t.float :avg
t.integer :tds
t.float :ydspg
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?

2

Supplying some further information:

Each table currently does not have a team_id - they each only have their
regular IDs.

Secondly, I parse data weekly so there are multiple week snapshops of
each table… for example:

Week 1 Snapshot houses 120 teams and their data (non-ordered) ids from
1-120.
Week 2 Snapshot houses the same 120 teams and their new data
(non-ordered) ids range from 121-240.
Week 3 Snapshot houses the same 120 teams and their new data
(non-ordered) ids
range from 121-240.
etc.
etc.

This applies to every table I have. In my model I currently show the
current week’s data only. I could simply overwrite this data, but I’m
saving weekly snapshots so that people can select previous week data or
data from farther back.

So, there needs o be an associative team_id that is assigned to every
team and so it’s the same for every table… i.e. 120 teams in
alphabetical order with team_ids that range from 1-120. This should
apply to each table.

How do I create the join table to accomplish this?

Thanks.

3

On Jun 24, 9:27 pm, “Älphä Blüë” [email protected]
wrote:

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

4

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?

I can google hack normalization and third normal form…

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

I’ll just be patient and do some more reading…

5

On Jun 24, 10:23 pm, “Älphä Blüë” [email protected]
wrote:

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

6

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

7

My new site won’t go up for another month or so. It will look very
professional - I’m great with flash, css, and programming (javascript
for example)…

I just learned a ton about the Normal Forms… Great stuff… I even used
it to normalize a table to 3NF

For instance, my Rushing Offenses table had the following fields

Rank
Name
Games
Carries
Net
Avg
TDs
Ydspgm
Wins
Losses
Ties
DateTime

I was pulling separate data each week and adding it to the table. So,
let’s say I had 4 weeks worth of data for 120 teams. Normalizing it, I
found the following issues:

1NF:

Are there any duplicative columns? No. Do we have a primary key? Not
really, the built in ID is irrelevant in this particular table. So, I
changed things around a bit.

Two Tables:

table ‘teams’

team_id
name

table ‘rushing_offense’
team_id
rank
games
Carries
Net
Avg
TDs
Ydspgm
Wins
Losses
Ties
DateTime

This normalized it for 1nf.

2NF:

Are there any subsets of data that apply to multiple rows? No, so the
above meets 2NF as well.

3NF:

Are all of the columns fully dependent upon the primary key? No, so it
does not meet 3NF.

Rank, Avg, and Ydspgm are not fully dependent on the primary key. I can
get the rank from sorting Ydspgm. I can find the Avg by doing simple
math (dividing net/carries) and I can find Ydspgm by (dividing
net/games). Therefore, these don’t need to go into my table either.

So, I ended up with 2 final tables: (3NF qualified)

table ‘teams’

team_id
name

table ‘rushing_offense’

team_id
games
Carries
Net
TDs
Wins
Losses
Ties
DateTime

It was fun understanding that and accomplishing this tiny little task.
I had heard but never truly understood what normalization meant until
today. Thanks for the heads up.

8

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