Composite foreign key in Doctrine

Question

Hi,

I'm not really sure I'm using the good terms, so I'll try to explain my problem first.

I have a cross-reference table, CompetenceCollab, containing the associations between people and their competences. This table has two primary keys, the people's id, and the competence's id. To this point, everything is ok.

However, the new feature I have to add is the following one : people must be able to add a rating to other people's competences. Basically, this means I have to add a new table, which will contain the note, who gave it, and somehow a link to the CompetenceCollab table. One colleague told me I can use what he called an "alternate key", defining two foreign keys, one for each primary column of CompetenceCollab, and telling somehow the database that each rating is associated with a competence and a people.

So my questions are the following :

• first of all, do this design seems completely mad, and if it is the case, how should I do ?
• I have to use Doctrine ORM in Symfony. Is this kind of thing possible using that ? If yes, could someone provide me a way of how to define it in the YAML file ?

Thanks for the help, feel free to ask questions in the comments, I don't know if I was really that clear.

Answer 1

This is 2 questions, but I'm only going to answer the first one because I don't know anything about the ORM tool in the question.

I'm not really sure what is meant by an "alternate key", but the choices that I would consider are (in order of preference):

1) Define a surrogate primary key on CompetenceCollab and use that to create a foreign key from the new table

2) Have both a people id and competence id field in the new table and create a composite foreign key to CompetenceCollab

If the Doctrine ORM has a decent feature set it should at least handle (1), as it is a pretty common scenario.