# Question about HABTM **URL:** \ **Category:** Rails\ **Created:** [June 24, 2009, 7:56pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979 "2009-06-24T19:56:11Z")\ **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 24, 2009, 7:56pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/1 "2009-06-24T19:56:11Z")
I’ve started looking into the uses of HABTM with linked tables. My greatest problem right now is visualizing and connecting some of my tables together with one another. Let me provide a brief example: Again, using football mechanics for emphasis: Five groupings: Offense, Defense, Special Teams, Other, and Ratings Offense group contains 13 tables Defense group contains 13 tables Special Teams group contains 5 tables Other group contains 5 tables Ratings group contains 1 table Total Number of Tables: 37 Each table houses 120 teams Each table has the exact same team names What things I know I will be doing with the data? I will be showcasing “individual teams” and providing a layout of all their data for all 37 tables. I will be running cross-comparisons of one team’s data with another team’s opposing data. My Question: Should I use HABTM and linked tables with my project in this particular instance? If so, what advice can you give me on possible pitfalls and expectations down the road? ---
**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 24, 2009, 9:39pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/2 "2009-06-24T21:39:46Z")
Thanks a bunch Marnen - appreciate the input. I agree with you on your concept of a teams table. I use mysql and each table houses up to 14 or 17 fields each. As a great example, look at my present site: [http://ncaastatpages.com](http://ncaastatpages.com) The new site will be a lot better. That’s the old php driven site and it gives you an idea of how much data each table holds. I will use your suggestion and create a teams table and assign a team ID. Do you by chance have a link for an up-to-date foreign keys migration plugin? I see a bunch of outdated ones and would hate to get the wrong one. Also, mysql was using myisam but I can just alter the tables to use innodb. My worry with innodb was that they require a lot more disk space. But, if I need to use innodb over myisam I will definitely do so. I’m not all that familiar with postgresql, other than it’s similar to oracle… ---
**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 24, 2009, 8:27pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/3 "2009-06-24T20:27:11Z")
Älphä Blüë wrote: > I’ve started looking into the uses of HABTM with linked tables. By “linked tables”, you mean DB tables associated by means of foreign keys, right? > My > greatest problem right now is visualizing and connecting some of my > tables together with one another. Database design principles are practically the same for Rails apps as for any other application. > Let me provide a brief example: > > Again, using football mechanics for emphasis: > > Five groupings: > > Offense, Defense, Special Teams, Other, and Ratings What do you mean by “groupings”? > Offense group contains 13 tables > Defense group contains 13 tables > Special Teams group contains 5 tables > Other group contains 5 tables > Ratings group contains 1 table And these tables are…? > Total Number of Tables: 37 That seems excessive, but since I don’t know your data, I can’t say for sure. > Each table houses 120 teams > Each table has the exact same team names Aha! Then there’s your first candidate for normalization. Create a “teams” table, containing ID, name, and whatever else is necessary, and refer to the teams in the other tables by team\_id. > What things I know I will be doing with the data? > > I will be showcasing “individual teams” and providing a layout of all > their data for all 37 tables. Then you probably want a Team model, which will fit in nicely with what I suggested above. > I will be running cross-comparisons of one team’s data with another > team’s opposing data. Ditto. > My Question: > > Should I use HABTM and linked tables with my project in this particular > instance? You should use associations on the Rails side and foreign keys in the DB. (There’s not a project I can think of where that’s inappropriate, provided that the project uses a DB!) > If so, what advice can you give me on possible pitfalls and > expectations down the road? Use the foreign\_key\_migrations plugin to set up foreign key constraints in your DB. If you’re using mySQL, use InnoDB tables so the constraints work; better yet, use PostgreSQL instead. Get familiar with DB table joins if you’re not already. Get used to making one query with a huge resultset, not many queries with tiny resultsets. Good luck! ## 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 24, 2009, 9:56pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/4 "2009-06-24T21:56:44Z")
Älphä Blüë wrote: > Thanks a bunch Marnen - appreciate the input. I agree with you on your > concept of a teams table. I use mysql and each table houses up to 14 or > 17 fields each. As a great example, look at my present site: > > [http://ncaastatpages.com](http://ncaastatpages.com) I’ll look, but I rather doubt that the site will tell me a lot about your DB… > The new site will be a lot better. That’s the old php driven site and > it gives you an idea of how much data each table holds. > > I will use your suggestion and create a teams table and assign a team > ID. Do you by chance have a link for an up-to-date foreign keys > migration plugin? I see a bunch of outdated ones and would hate to get > the wrong one. Use the one at Github. I think the URL is [http://github.com/harukizaemon/foreign\_key\_migrations](http://github.com/harukizaemon/foreign_key_migrations) . Check out his other stuff while you’re there. > Also, mysql was using myisam but I can just alter the tables to use > innodb. My worry with innodb was that they require a lot more disk > space. But, if I need to use innodb over myisam I will definitely do > so. MyISAM gets you speed at the expense of referential integrity and transaction support. For most applications, speed isn’t at such a premium that MyISAM is at all a good idea. > I’m not all that familiar with postgresql, other than it’s similar > to oracle… Think of it as mySQL without the suckage. 🙂 You don’t have to choose between speed and referential integrity; the engine is extensible, so there are GIS solutions that actually work; the procedural language is actually useful; the syntax is closer to the SQL standard…and on and on. I don’t recommend mySQL for most applications – I know it’s popular, but it’s not that good. ## 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 24, 2009, 11:35pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/5 "2009-06-24T23:35:01Z")
Älphä Blüë wrote: > I just finished installing postgres on my linux server. Going to do the > same on my windows environment and then begin some testing. OK. Note that mySQL will also work for what you’re talking about, but… > As far as > my current project goes, is there an easy method of changing mysql to > postgres with rails? I have a mid-size project and would hate to have > to recreate it from scratch… Use your migrations and/or schema.rb to create the new DB, dump the data, load the data. ## 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, 12:56am UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/6 "2009-06-25T00:56:47Z")
Hi marnen, Yeah I’m going to have to stick with mysql. I’m using Ruby 1.9.1 on linux prod, linux test, and windows develop and can’t get either pg or postgres gem installed on any of the three. I will still try out the foreign key migrations plugin though. Thanks a bunch. ---
**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 24, 2009, 10:51pm UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/7 "2009-06-24T22:51:35Z")
I just finished installing postgres on my linux server. Going to do the same on my windows environment and then begin some testing. As far as my current project goes, is there an easy method of changing mysql to postgres with rails? I have a mid-size project and would hate to have to recreate it from scratch… ---
**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:06am UTC](https://www.ruby-forum.com/t/question-about-habtm/170979/8 "2009-06-25T04:06:01Z")
Älphä Blüë wrote: > Hi marnen, > > Yeah I’m going to have to stick with mysql. I’m using Ruby 1.9.1 on > linux prod, linux test, and windows develop and can’t get either pg or > postgres gem installed on any of the three. I strongly urge you to use Ruby 1.8 instead. There are too many incompatibilities with 1.9. (Someone at tonight’s user group meeting just told me about [http://isitruby19.com](http://isitruby19.com)…) > I will still try out the foreign key migrations plugin though. Yes, that should still work. > Thanks a bunch. You’re welcome! ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid