Recreating the rental order model after go-live
A rental company's Mendix app stores orders in an Order entity. Each Order carries CustomerName and CustomerEmail as strings copied from the selected Customer at creation, plus NetTotal, which a microflow recalculates by looping the order's OrderLines every time the order is saved. The domain model has Customer 1-* Order with delete behaviour Prevent, and a many-to-many Customer-Product association used for a 'favourites' feature. Customer service wants to delete customers who have left the company; finance wants monthly revenue per customer city. The Order table now holds 400,000 rows, and the finance microflow, which loops all orders inside a loop over customers, takes over 15 minutes. Deleting a customer throws an error.
1. Which modelling decisions are responsible for the symptoms, and how would you remodel them without breaking existing pages?
Read the worked answer
Attributes that duplicate another entity's data
- CustomerName and CustomerEmail are a denormalised copy of Customer. Fix: remove them and show the values through the association (a data view over Order with a nested data view over the associated Customer, or an attribute path in the page).
- Only keep a copy where the business genuinely needs a snapshot (e.g. the address used on an old invoice). If so, rename it (InvoiceAddress) so nobody mistakes it for a lookup.
Derived value stored and recalculated in a loop
- NetTotal is a function of OrderLines. Either persist it and maintain it at the moment lines change (after-commit event on OrderLine, or in the same microflow that changes lines), or stop storing it and aggregate at read time.
- A calculated attribute is the third option, but it is computed in memory: it cannot be used in an XPath constraint or in database-side sorting, so it is only acceptable where the result set is already small.
Modelling change without breaking pages
- Add the association-based path first, change pages and microflows, and only then delete the old attributes; a rename in one step breaks every page and microflow that references the attribute.
2. Customer service must be able to 'delete' a customer, but invoices must survive for seven years and finance must query historical customers. What do you do with the association and the data?
Read the worked answer
Do not switch the association to Cascade. Cascade would delete the customer's orders, which are financial records; the error the team sees today is Prevent doing its job.
Archive instead of delete
- Add Customer.IsArchived (Boolean, default false) and an ArchivedOn date.
- Provide an 'Archive customer' microflow that sets the flag, commits, and optionally ends any active sessions; do not offer a delete button to the Customer role.
- Every page and microflow that lists customers adds
[IsArchived = false]to its XPath. Put that constraint in the entity access rules for the role as well, so a forgotten retrieve still cannot surface archived records. - Keep a real delete path only for a back-office administrator role, and only after checking that the customer has no orders (count via association), so Prevent never fires unexpectedly.
Why this beats cascade Archiving keeps the association intact, so the seven-year finance queries and the order-to-customer joins still work, and a mistaken archive is reversible by clearing the flag.
3. How would you rebuild the per-city revenue report so it stays fast at 400,000 orders, and what would you check to confirm your hypothesis before changing anything?
Read the worked answer
The current microflow is O(customers x orders) in the app layer. Every order is transferred to the runtime and grouped in memory; the database does none of the work.
Approach
- Measure first: check the duration of the microflow in the app log/Mendix Cloud metrics and confirm which retrieve dominates. A single database Aggregate list operation (Sum of NetTotal, grouped by Customer_City) or an OQL query gives the same numbers with one round trip.
- If grouping by an attribute of an associated entity is awkward, aggregate per customer with a Retrieve + Aggregate over the association, then group in the database through OQL rather than a nested loop in the modeler.
- Make sure the path used for filtering/joining is indexed; association columns are indexed by the platform, plain attributes used in XPath constraints are not unless you mark them.
Sanity checks Compare totals against a manual SQL count for one city, and test with the largest city to make sure no single group still pulls a huge list into memory.