I am in the process of trying to model a transportation module for an ERP type system using C# and EF Core. This service is responsible for managing customer pickups and company-owned truck deliveries. Customer pickups contain a list of orders and trucks contain a list of stops and those stops contain a list of orders.
The primary interface for managing pickups and trucks is through a REST based api. Order creation/update/cancellation events are being received from an ordering module via a service bus queue.
- At order entry, an order is assigned an attribute to specify if it is a customer pickup or delivery via a truck. However, it is up to users within this transportation module to associate those orders to a specific pickup or truck/stop instance.
- An order can be associated to only a single pickup or truck stop at a given time.
- Orders have properties which include a status and shipping metrics (dimensions, weight).
- Orders on a truck cannot exceed a certain volume or total weight.
- Order updates can be received at any time, even after a shipping assignment is made. Those updates can change the shipment type (user decides to pick up order vs. having it shipped to them) or order build which would alter the shipping metrics. If shipping type is changed, order should be unassociated to any current pickup or truck stop.
- Trucks have a status of open/in-transit/closed with each stop on the truck having a status of open/delivered.
- Users can mark a truck as in-transit only if all orders have a status of produced.
- Once truck is in-transit, users mark each stop as delivered once the delivery has been made. Only after all stops are marked as delivered, can the truck status be updated to closed.
- If an order is cancelled, it needs to be automatically removed from either the pickup or truck it may be associated to.
We are using a relational database (SQL Server) for storing the individual entities. My question is really around how to model these various aggregate roots/entities/value objects/domain services/domain events as well as the database tables backing them.
- Have aggregate roots of Pickup, Truck and Order.
- Pickup has a list of value objects containing linked order ids.
- Trucks have a list of stop entities and a list of value objects containing linked order ids, order status and order shipping metrics.
- Other options considered - add nullable foreign keys directly to order that reference a pickup or truck stop; additional option is to have an OrderAssociations table that maps orders to a pickup or truck stop.
In order to enforce business rules for the truck though this is where things get a bit interesting. If adding an order to a truck, the total weight and volume of all other orders needs to be taken into account and an error should be returned if the order will cause the truck to exceed the prescribed thresholds. If an order update is received for an order already associated to a truck and that order will cause the truck to exceed the allowed thresholds an alert needs to be fired and the truck cannot be marked as in-transit until the issue is resolved.
- When an order update is received, we need to know if it is associated to a pickup or truck stop and if it is, that object will need to be notified. If we have separate tables for PickupOrders and TruckStopOrders, determining the association doesn't exactly seem efficient as a query would need to be made to both tables. In addition, we'd need to load the entire truck of stops/orders to call an update order method on the truck aggregate. How would you recommend this order update be handled? Is this an application level event handler to update the order entity itself as well as the truck? Does the order entity get updated and raise a domain event that the truck is somehow notified of? Curious on thoughts of if this logic belongs in the domain layer or application layer.
- Are truck stops value objects or entities? They only exist within the context of a truck. However, they do have a status associated to them (open/delivered) and a list of associated orders.
- It would be nice for orders to maintain a reference to the pickup or truck stop that they are associated with. However, doing so would couple the truck stop / pickup and order so any order updates would require updating both in the same transaction. Not sure if this would be managed via a domain service or application layer?
- If maintaining a separate truck stop orders table, what is the best way to keep the order status/metrics in this table in sync with the orders table?
Happy to provide additional clarification where needed. Any thoughts are appreciated.