# Join Tables **URL:** \ **Category:** Rails\ **Created:** [June 25, 2009, 3:27am UTC](https://www.ruby-forum.com/t/join-tables/170998 "2009-06-25T03:27:05Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 3:27am UTC](https://www.ruby-forum.com/t/join-tables/170998/1 "2009-06-25T03:27:05Z")
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? ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 3:54am UTC](https://www.ruby-forum.com/t/join-tables/170998/2 "2009-06-25T03:54:37Z")
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. ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 4:13am UTC](https://www.ruby-forum.com/t/join-tables/170998/3 "2009-06-25T04:13:19Z")
On Jun 24, 9:27 pm, “Älphä Blüë” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 4:23am UTC](https://www.ruby-forum.com/t/join-tables/170998/4 "2009-06-25T04:23:36Z")
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… ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 4:50am UTC](https://www.ruby-forum.com/t/join-tables/170998/5 "2009-06-25T04:50:43Z")
On Jun 24, 10:23 pm, “Älphä Blüë” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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… 🙂 ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 6:47am UTC](https://www.ruby-forum.com/t/join-tables/170998/6 "2009-06-25T06:47:28Z")
Ä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. > **[Enterprise Rails](https://books.google.com/books?id=thTju-4duY4C&pg=PP1&dq=enterprise%2Brails&ei=8gBDSrrsEoLGlQTQnJX8Dg)** > > What does it take to develop an enterprise application with Rails? Enterprise Rails introduces several time-tested software engineering principles to prepare you for the challenge of building a high-performance, scalable website with global reach.... ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 6:12am UTC](https://www.ruby-forum.com/t/join-tables/170998/7 "2009-06-25T06:12:35Z")
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. ---
**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\ **Post date:** [June 25, 2009, 2:50pm UTC](https://www.ruby-forum.com/t/join-tables/170998/8 "2009-06-25T14:50:49Z")
Thanks a lot Chris. I appreciate the link and will definitely read up on it.