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?
--
Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/.
On Jun 24, 9:27 pm, "Älphä Blüë" <rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt> wrote:> 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_migrationsOK. 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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
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.. -- Posted via http://www.ruby-forum.com/.
On Jun 24, 10:23 pm, "Älphä Blüë" <rails-mailing-l...@andreas-s.net> 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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
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. -- Posted via http://www.ruby-forum.com/.
Ä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 -- Posted via http://www.ruby-forum.com/.