Problem with join table

rubyonrails-talk
1

Hi all,

I am trying to model with AR some tricky db tables.

The table names are in caps and any constraints below or next to the column.

The tables:

USERS id PK username etc

PROFILES id PK user_id FK references users(id) name

unique (id, user_id)

TELEPHONES id PK user_id FK references users(id) name

unique (id, user_id)

PROFILES_TELEPHONES user_id profile_id telephone_id

constraint FK (profile_id, user_id) references profiles(id, user_id) constraint FK (telephone_id, user_id) references telephones(id, user_id) PK (user_id, profile_id, telephone_id) unique (profile_id, telephone_id)

The user_id is placed in the join table to ensure that the owner of the profile and telephone are the same. If it is not there referential integrity could be maintained, but the domain model would not make sense.

AR complains with deprecation warnings if I use a HABTM relationship between telephones and profiles, but this seems to keep the user_id present. If I switch to a has_many, :through, it seems to require a single primary key on the table.

Any thoughts how to model this better with AR.

Thanks!

2

Anyone?

3

Hi all,

I am trying to model with AR some tricky db tables.

The table names are in caps and any constraints below or next to the column.

The tables:

USERS id PK username etc

PROFILES id PK user_id FK references users(id) name

unique (id, user_id)

TELEPHONES id PK user_id FK references users(id) name

unique (id, user_id)

PROFILES_TELEPHONES user_id profile_id telephone_id

This should either be

PROFILES_TELEPHONES profile_id telephone_id

and use HABTM. HABTM is defined as a stupid-simple join table between two (only) tables. It must not have a primary key. It must have only two columns -- model_a_id and model_b_id.

or

SOME_OTHER_NAME id (PK) user_id profile_id telephone_id

and use has_many :through instead of HABTM.

constraint FK (profile_id, user_id) references profiles(id, user_id) constraint FK (telephone_id, user_id) references telephones(id, user_id) PK (user_id, profile_id, telephone_id) unique (profile_id, telephone_id)

The user_id is placed in the join table to ensure that the owner of the profile and telephone are the same. If it is not there referential integrity could be maintained, but the domain model would not make sense.

AR complains with deprecation warnings if I use a HABTM relationship between telephones and profiles, but this seems to keep the user_id present. If I switch to a has_many, :through, it seems to require a single primary key on the table.

Yes, this is how it works. You would add an id column to your "join model" table, and AR would be fine and dandy with that. You don't ever have to use that id for anything, but it has to be there.

Walter