How to model a relationship with different roles of users

Question

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.

1. We can have "fields" customer_id, manager_id, courier_id in the order "model" (table).

2. 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.

3. Have multiple "pivot" tables like customer_order, manager_order, courier_order.

Please suggest the best option to choose for such a business domain.

Thanks.

Answer 1

Option 3 is considered the best practice in this case because it lets the RDBMS do what it is designed to do. You have the best options for controlling relationships with declarative referential integrity and your queries will be simple and direct. If you specifically want to make this option work like Option 1, insofar as the relationships are constrained to many to one (many orders to one customer each for example) then add a unique constraint on the order_id foreign key column in each intersection table. However, in practical terms, you might actually want many-to-many because you might assign different managers or couriers over time and that history may be important. It all depends on your business rules.

Option 2 is considered a "SQL Code Smell" (polymorphic association)

Option 1 might be reasonable as well, depending on your business rules, but some people don't like it because it might result in "sparsely populated columns" (again, depending on your business rules).

Answer 2

Option 1.

Unless an Order can have multiple Customers, or there are several attributes that belong to the order-customer relationship (ie are functionally dependent on (order_id,customer_id)). Then Option 3.