Composite Foreign Key in the Doctrine

Question

I'm not sure that I am using good conditions, so first I will try to explain my problem.

I have a cross-reference table, CompetenceCollab , which contains associations between people and their competencies. This table has two primary keys, a user identifier and a competency identifier. At this point, everything is in order.

However, the new function that I have to add is the following: people should be able to add ratings to other people's competencies. This basically means that I have to add a new table, which will contain the note that it gave, and somehow link to the CompetenceCollab table. One of my colleagues told me that I can use what he called an “alternative key”, defining two foreign keys, one for each primary column of CompetenceCollab , and somehow tell the database that each rating is related to competency and by people.

So my questions are:

• First of all, this design seems completely insane, and if so, how should I do it?
• I need to use Doctrine ORM in Symfony. Can this be used? If so, can someone provide me a way to define it in a YAML file?

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

Answer 1

I understand this is an old question, but composite foreign keys are supported with Docrine 2.1. See: http://readthedocs.org/docs/doctrine-orm/en/latest/tutorials/composite-primary-keys.html

Answer 2

These are 2 questions, but I will only answer the first one because I know nothing about the ORM tool in the question.

I'm not quite sure what is meant by the "alternate key", but the choice I would consider is (in order of preference):

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

2) The id and competency identifier in the new table and create a composite foreign key for CompetenceCollab

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