I think to begin with, an appropriate change to your data model would
be:
create table answers_questions (
id int not null autoincrement,
question_id int unsigned not null,
answer_id int unsigned not null,
constraint fk_qid foreign key (question_id) references questions(id),
constraint fk_aid foreign key (answer_id) references answers(id),
primary key (id)
) ENGINE=InnoDB;
In general, it’s considered better practice to have primary keys be
‘meaningless’ and not use them to implement what essentially is a
business
rule.
You can then implement uniqueness on the two foreign keys directly in
active
record using the built in validation capabilities it provides to ensure
that
you don’t get duplicates.
You have a choice to put the validation logic in the database or put it
in the
ActiveRecord models. There are reasons to go either way, but in this
case
pulling the valdation out of the database and putting it in the
ActiveRecord
models looks like it may be easier nd give you more flexibility.
I don’t believe changing my data model is the right thing here. For a
start it’s already helped me identify this potential problem
Also, those rows are already unique by the combination of answer_id and
question_id. Adding a new primary key called id is meaningless.
An additional problem is that ActiveRecord includes all columns from the
link table when joining the linked table, so the id field would collide
(the link table id would take preference as I understand).
Technically, the primary keys in this instance aren’t implementing a
business rule. The link table should never include duplicate link
records. At the end of the day, I’m trying to do something that
violates this rule, which is wrong. Perhaps the best course of action
is just to figure out a graceful way to handle it in my code. I don’t
know how to override the @question.answers.push method, if this were to
be the best option.
I agree that putting an id column in the join table is a bad idea
because of
the ‘clobbering’ that will happen. The documentation even mentions
this.
Not to say that you can’t, but you would then have to create a different
relationship between the models
Do you think ActiveRecord’s behaviour is correct? Or should it check
for duplicates itself here? Without a compound primary key this might
have gone undetected and I think DHH’s “single layer of cleverness”[1]
post has people believing the db shouldn’t be checking this stuff.
