I am creating a new project and currently modeling the relevant business domain. I am hesitating to choose one solution over another.
Consider the following case.
There is an order, the order has a customer who made it, an order will have an assigned manager after some state changes and next order will be assigned to a delivery guy.
I don't know how to model this relationship, not strictly in SQL, but in general from the business domain perspective.
I have tried to trace these changes, however figured out that any redux dispatch action causes this issue.
- We can have "fields"
customer_id, manager_id, courier_idin the order "model" (table).
Use pivot tables with additional information of relationship type
order_id = 31 user_id = 22 rel_type = "customer"
This will be quite tricky to query such a relationship.
- Have multiple "pivot" tables like
Please suggest the best option to choose for such a business domain.