Composite primary keys or surrogate primary key on relationship entities (entities that are also relations) in Doctrine2

Question

I have a large database where I have entities that are relations. This exactly because of the following comment in the Doctrine 2 manual: I needed to store additional attributes in our relations and because of this the relation became an entity.

Normally we use generated ids for entities and composite keys for relationships. In this case the entity is a relationship, so that makes the choice a bit more complicated...

Be aware of the fact that I'm not talking about natural keys here. I am using a composite key which consists out of id's which are integers created using an auto increment strategy in their respective owning entities.

I see some disadvantages of using composite primary keys on the relationship entity:

• I need to call persist in between creating the entities and their relations (to generate the key on the entity before I can use it as a foreign key in my composite primary key).
• To relate additional entities to the relationship entity we need to add columns for the ids used in the composite key again in those entities. If I would use a surrogate primary key I only need one column which holds that one id.

I see also some advantages of the using composite keys for example:

• They eliminate the need for certain joins queries, instead you can build queries to directly extract information using the composite keys.

But the most important argument for using composite keys for now is the following:

• When a relationship between a User and SomeEntity gets deleted and later created again (a scenario which is likely to happen in our application model) we need to restore access to all other related entities too. If the relationship between User and SomeEntity is created using an auto increment strategy on a surrogate primary key. The restored (new) relationship will get a new primary key (id). Because of this the related entities (stored using the "old" key) are no longer accessible. If I'd use a composite key on the relationship I could simply restore the relationship (it gets the same composite key values) and by that I have immediately access to the other related entities again.

Are there any people who have experience with a similar scenario? Are there other solutions to get this working properly. Are there any important disadvantages or advantages (when using Doctrine ORM) that I missed.

One alternative I can think of is by adding a state to the the User SomeEntity relationship. Instead of deleting the entry we could change the state to disabled, enabled. In that way we could use a surrogate key instead of a composite key because the entry will never be deleted...

Feedback is greatly appreciated...

For the people that are interested there was also a nice discussion on surrogate vs composite keys on Stack Overflow here: (click)

Answer 1

I would propose to make your two foreign keys a composite primary key.

Your association entity would be identified by the ids of its two relations.

Theorically, Doctrine's UnitOfWork/CommitOrderCalculator should be albe to insert elements in correct order, and thus, you wouldn't have to deal with persist order.

Your mapping would look like this at the end (annotation example):

<?php

class UserGroup
{
    /**
      * @ORM\ManyToOne(targetEntity="User")
      * @ORM\Id
     **/
    public $user;

    /**
      * @ORM\ManyToOne(targetEntity="Group")
      * @ORM\Id
     **/
    public $group;
}