Problem with join table

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!

Anyone?

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