Doctrine - a database structure for storing documents, and participants in the workflow?

Question

MySQL

There is an information system (document flow).
It contains a list of documents that can change their status (under approval, under revision, closed, etc.).
The status changes when a user with a certain role performs the corresponding operation.
For example, the author of the document presses "publish" and the document changes its status from "under revision" to "under approval".
There are nuances:
1. there are approvers in the document (for example, initiator, curator) with the manyToOne connection (the document has only one initiator, author, curator ...)
2. there are approvers with the manyToMany connection (any user with the role of lawyer can approve the document) . If at least one lawyer agrees, the document moves to the next stage
3. there are coordinators with a manyToMany relationship (there are many performers in one document). Only if everythingthe executors agreed, the document moves to the next stage.
As we implemented earlier:
single roles were stored directly in the document table (initiator_id, curator_id, author_id):

manyToMany agreeing (see point 2), which, by roles, were stored in a separate agreement table:

while initially the user_id=null field , as only someone with the role_id role coordinated the document, his ID was put in the user_id, the state was set to 1 and the document went further at the same time, all users from the list of the executor have state=0 by default. As soon as all state became 1, the document was transferred to the next stage
, the manyToMany approvers (see point 3) were stored in separate tables corresponding to a separate role, for example, executors:

. Then, it was necessary to rewrite everything in Symfony. And I began to think about how to properly organize the storage of matchers, from the point of view of Doctrine. The old approach was no longer appropriate here. At first, I created the following scheme:

Here I also store single approvers in the document, Approvers from point 3 in the doc_signers
table With those who approve by roles (point 2), I don’t know what to do yet.
Help competently organize entities. If there is already some kind of methodology for solving such problems (I personally did not find it), please post a link.
Thank you!
UPDin the context of Symfony + Doctrine, it will be more correct to talk about the structure of entities and relationships between Entities

Answer 1

The task is not difficult. But you have confused yourself. You won't find a method. You need to learn how to analyze your business process and divide it into algorithm and context. It would be wrong to solve this problem for you, so here are some tips for you:
1. Throw out from the document all the signs responsible for coordination.
2. Enter a new entity Reconciliation.
3. Link the Document to the Approval entity with a single link. So you clear the document of unnecessary information to it. The link must be stored on the Owning Side Relation side, but it should be possible to get the Inverse Side Relation entity on the Document side.
4. If I understand correctly, then your document can be aligned according to three different algorithms. If so, then use the opportunity of the Class Table Inheritance doctrine. You will get one link on the document side, but different validation algorithms on the Reconciliation side.
5. In each of the Approvals, implement the isApproved method with your own logic.
6. After such a separation, you will be able to build algorithms, attaching to each type of Agreements separately, without paying attention to others.

Answer 2

Greetings! I have done three similar workflow projects over the past year. I would like to share with my decision how I organized the structure of the tables (they are also entities in doctrines).
My projects had several approval routes. So I kept them separate. And he called them workflow (business process)

Workflow: (Маршрут согласования)
    id
    name (Название маршрута согласования)
WorkflowPoint: (Точка согласования)
    id
    title (Название точки. Например "Согласование фин директора")
    workflow_id (Связанный маршрут)
    user_id (Пользователь, который рассматривает документ в этой точке согласования)
    order_id (Порядковый номер точки в маршруте)
    roles (Права пользователя, в данной точке согласования)

In my projects, several file attachments could be stored in one approval document. Therefore, the File and the Approval Document were kept separately.

Approval: (Документ согласования)
    id
    title (Название документа)
    author_id (Инициатор)
    state = 0 (Состояние документа. 0 - черновик. 1 - в процессе согласования. 2 - одобрено. 3 - отказано. 4 - архивировано)
    workflow_id (Маршрут, по которому движется данный документ. По умолчанию null, кроме того, когда состояние документа в процессе согласования)
    workflow_point_id (Текущая точка согласования в маршруте. При одобрении, указывается следующая точка)
    approval_user_id (Пользователь, который должен рассмотреть документ в текущий момент. Это поле чисто для удобства в поиске)
Attachment: (Файл-вложение)
    id
    approval_id (Связанный документ)
    file_path (Путь к файлу)
    author_id (Пользователь, который вложил файл)

I have greatly simplified the structure just to give you an idea to think about. In real projects it was much more difficult