0

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.

EER Diagram

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you for your answer. However, For the option 1), Do we lose the project relation on the project_data table "X" if I set another project_data "Y" and "Z" as values in the project properties "project_data_id" and "project_data_waiting_id"? Because I want to keep the fact that the project_data was created for one specific project, even if this project_data is not used anymore in the project table
And for the option 2) : It is indeed an interesting approach, however, I'm actually designing this to build a kind of a "revision" system for project_data entries. Each project could have a project_data that is "actual", another that would be awaiting for approval. But I don't want to lose the history of the other previous project_data entries that wouldn't be in that two cases. With that design, wouldn't it be difficult to retrieve the current project_data "waiting" for approval and the current that is accepted?
Option 1: Yes, you will lose it. If you want to keep track of it you should go for option two and add a history status or something like that. Option 2: you can just build a query to get the project_data with a specific status belonging to that project.
Indeed, I think I will use the option 2) and take care of updating correctly the statuses of my project_datas. Thank you !

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.