One-to-one, compound primary key, naming conventions

Rails
1

My understanding is that, given tables Alpha and Beta that the table
which holds the 1-to-1 relation will be called Alpha_Beta (or is it
alpha_beta?). So far so good.

alpha_beta will have, lets say, two fields: alpha_id and beta_id.
Seems
that it’d be a good idea for those two fields to form a compound primary
key, ensuring there are no duplicates.

However, RoR doesn’t support compound primary keys. So, does table
alpha_beta require its own id for a primary key? If so, that’s what
drives me up the wall about Microsoft Access. Else, the option is to
use
alpha_id, or beta_id, as the primary key. Either of those options seem
sub-optimal to my mind, and, really, the choice would be entirely
arbitrary. Unless, for consistency, it’s just the “first” one, which,
really, while predictable, is still arbitrary in that it’s no better
than
the “second” one (alphabetically wise).

Do I understand how RoR works correctly? If I made an error with the
naming conventions, or anything else, please do correct me :slight_smile:

also, it’s standard to have three databases:

dummy_test
dummy_development
dummy_production

yes?

thanks,

Thufir

2

If your alphas_betas table contains no additional keys, then you use a
“has
and belongs to many” relationship in rails… This is the only out of
the
box compound key that’s supported, but that’s because you don’t have a
model.

class Alpha < ActiveRecord::Base
has_and_belongs_to_many :betas
end

class Beta< ActiveRecord::Base
has_and_belongs_to_many :alphas
end

@beta = Beta.find(1)
@alphas = @beta.alphas

Keep in mind that the join table should be pluralized on both sides and
should be in alphabetical order… alphas_betas. (that’s the
convention).

Now, if your join table has additional attributes, then, by the Rails
philosophy, it’s now not really a join, but rather its own model with
foreign keys. Thus, you use a has_many :through relationship.

I recommend reading some about has_and_belongs_to_many and has_many
:through. You can find lots on google, as well as in the Agile Web
Development book.

Does that help?

3

Oh, and yes… three databases. One for development, one for tests (you
should embrace unit testing), and one for when you go live.
(production).

4

On 6/21/07, Thufir [email protected] wrote:

alpha_beta require its own id for a primary key? If so, that’s what
drives me up the wall about Microsoft Access. Else, the option is to use
alpha_id, or beta_id, as the primary key. Either of those options seem
sub-optimal to my mind, and, really, the choice would be entirely
arbitrary. Unless, for consistency, it’s just the “first” one, which,
really, while predictable, is still arbitrary in that it’s no better than
the “second” one (alphabetically wise).

To add to what Brian said, yes, alphas_betas needs it’s own “id”
column. You’re right that RoR (ActiveRecord, really) doesn’t support
compound PK’s. AR will maintain the uniqueness, but you can add a
unique index on alpha_id, beta_id. (If the table is large, you should
index both columns for retrieval anyway.)

It won’t work to let alpha_id or beta_id be the PK, because there will
be duplicates of each (otherwise, it isn’t a many-many relation).

If you create your tables with migrations (which you should; a
migration stub is generated for you when you generate a model), you
don’t even specify the id column. Rails adds it for you.

5

On Jun 21, 3:17 pm, “Brian H.” [email protected] wrote:

@Bob:

You don’t need an id column on the join table if it’s a
has_and_belongs_to_many association… you only need it with has_many
:through. I believe that habtm has it’s place for simple relationships.
However, it may be beneficial to use has_many :through in this case.

Just want to make sure the OP wasn’t getting confused by us :slight_smile:
[…]

Nope, I’m confused by the replies.

what’s with the has and belongs to? That exists only on the OOP side
of things, composition and inheritance, yes? it doesn’t exist in
databases, to my knowledge at least…

by the way, to my thinking, tables alpha and beta would have, say,
four fields each, for example, including the id field. The table with
the relation, alphas_betas, would, to my thinking, not need more than
two fields.

-Thufir

6

Yes… has and belongs to many is on the OO side. Have you read through
“Agile Web D. with Rails” yet? It covers this topic much better
than I can.

7

@Bob:

You don’t need an id column on the join table if it’s a
has_and_belongs_to_many association… you only need it with has_many
:through. I believe that habtm has it’s place for simple
relationships.
However, it may be beneficial to use has_many :through in this case.

Just want to make sure the OP wasn’t getting confused by us :slight_smile:

8

On Thu, 21 Jun 2007 10:15:08 -0500, Brian H. wrote:

If your alphas_betas table contains no additional keys, then you use a
“has and belongs to many” relationship in rails… This is the only out
of the box compound key that’s supported, but that’s because you don’t
have a model.

I’m not sure what a model is, but if there’s a model then it’s possible
to have a compound key composed of fields alpha_id and beta_id in table
alphas_betas?

class Alpha < ActiveRecord::Base
has_and_belongs_to_many :betas
end

class Beta< ActiveRecord::Base
has_and_belongs_to_many :alphas
end

@beta = Beta.find(1)
@alphas = @beta.alphas
[…]

This, I take it, is the model describing that alpha and beta belong to
each other which allows the compound key?

Now, if your join table has additional attributes, then, by the Rails
philosophy, it’s now not really a join, but rather its own model with
foreign keys. Thus, you use a has_many :through relationship.

Yes, I think I read something about this. In fact, I know I did. What
struck me was trying to figure out what would be in the join table
which wouldn’t already be in either alpha or beta to begin with. There
was an example, I do recall, but it escapes me at the moment.

I recommend reading some about has_and_belongs_to_many and has_many
:through. You can find lots on google, as well as in the Agile Web
Development book.

Does that help?
[…]

Yes.

the post was designed to be mildly inflammatory to get some responses
(mission accomplished), but, for me, it’s a definite side-issue which I
won’t be further reading up on, at least intentionally, until I have my
ducks in a row.

-Thufir

9

On Thu, 21 Jun 2007 10:15:48 -0500, Brian H. wrote:

Oh, and yes… three databases. One for development, one for tests (you
should embrace unit testing), and one for when you go live.
(production).
[…]

Thanks. It seems odd to use:

dummy_development
dummy_test
dummy_production

but to never use just “dummy” by itself. Oh well.

Thanks,

Thufir

10

On Thu, 21 Jun 2007 23:45:57 -0500, Brian H. wrote:

Yes… has and belongs to many is on the OO side. Have you read through
“Agile Web D. with Rails” yet? It covers this topic much better
than I can.
[…]

Not until I set up p2p, which won’t be until I upgrade to fedora 7,
which
won’t be until I re-install win2k, which won’t be until I back up my
data…my kingdom for a horse :wink:

My immediate interest in RoR is just to get it configured for a legacy
database, really nothing more than that.

On this topic, though, it seems that the OO side is describing things
which don’t exist on the database, which strikes me as a questionable
practice, and that what exists on the database might not be expressed in
RoR. However, I’m not at all well-informed on the topic!

In any event, it was largely just an idle question about a curious
statement I’d come across while flipping through a book.

-Thufir

11

Thufir,

RyanB has done a great screen cast on this that you should watch.

It’s called Two Many-to-Many and is on his website at

Reagrds

12

On Thu, 21 Jun 2007 16:22:23 -0400, Bob S. wrote:
[…]

To add to what Brian said, yes, alphas_betas needs it’s own “id” column.
You’re right that RoR (ActiveRecord, really) doesn’t support compound
PK’s. AR will maintain the uniqueness, but you can add a unique index on
alpha_id, beta_id. (If the table is large, you should index both columns
for retrieval anyway.)

Hmm. I read mention of that idea, but at the moment am not enamored of
it.

It won’t work to let alpha_id or beta_id be the PK, because there will
be duplicates of each (otherwise, it isn’t a many-many relation).
[…]

Right, but any combination should only occur once. This would typically
be achieved by making alpha_id and beta_id a compound key, to my
knowledge. How are duplicate combinations prevented?

thanks,

Thufir