Question about HABTM

Rails
1

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?

2

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

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…

3

Ä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
[email protected]

4

Ä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

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 . 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. :slight_smile: 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
[email protected]

5

Ä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
[email protected]

6

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.

7

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…

8

Ä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…)

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
[email protected]