Doctrine 2 : Multiple relations between two tables

Question

I have a problem more concerning database relations than Doctrine itself. I have a table "project" and a table "project_data". My table "project_data" is ALWAYS linked to a project entry. However, in my table "project", I can have two references to a project_data entry : project_data_id, and project_data_waiting_id. However, these references can be null and have no relation with the "project_id" that is set in project_data table.

Question :

• How to define all these relations? I want to be able to have project entries without any project_data references.

• How to handle it with Doctrine? I'm kind of new with Doctrine and with database design, and I am a bit lost between all the joins that I have to do between my tables.

I join you a diagram to have a better idea of what I want to do.

Thank you.

Answer 1

In this case I will give you two options (assuming project_data always has only one project):

First:

project
- id
- project_data_id
- project_data_waiting_id

project_data
- id
- name

In that case you can define two one-to-one relationsships on your project class. Look at http://docs.doctrine-project.org/en/latest/reference/association-mapping.html#one-to-one-bidirectional for more information how to handle this.

Second: option:

It's also possible to create a many-to-many relationship and give your project_data a status. It would look like this:

project
- id

project_data
- id
- name
- project_id
- status_id

project_data_status
- id
- name

In this case project will have a many-to-one relation with project_data and project_data will have a one-to-many relation with project_data_status. This solution gives you more flexibility. You can add as many project_data objects to project as you like.

How to define relations in doctrine2 can be found on the same page I already provided in this post.

Hopefully this will point you in the right direction.