3 ms·
Everyone who the store is selling to is a customer; an employee is a customer too except the price might be zero. What if a field is added to customers table: e
by noisy_boy 6y ago
Everyone who the store is selling to is a customer; an employee is a customer too except the price might be zero. What if a field is added to customers table: employee_id (would be null by default as most customers would not be employee). Then the query can be run without any union and bypassing stores/employees/employee_markouts etc:
SELECT
meal_items.*,
customer_id,
customers.employee_id
FROM customers
INNER JOIN customer_orders
ON customers.id = customer_orders.customer_id
INNER JOIN customer_order_items
ON customer_orders.id = customer_order_items.customer_order_id
INNER JOIN meal_items
ON customer_order_items.meal_item_id = meal_items.id
Of course this means that customer_id will never be null but that kind of is the point.
This can also be approached by adding a customer_id field to the employees table instead (in this example the no. of employees and customers are both 20000 but typically the no. of employees is less than the no. of customers so adding customer_id to employees table would be more typical - in which case the query would obviously change).
- kroolik 6y agoI think this is called denormalization, right? What you could try is to instead of modifying the domain for this specific use-case, create a read model instead (if you don't want to pollute your domain) . Sometimes its very easy to write data, but reading is a pain. For such cases, you can prepare read models that contains all the data necessary.
- noisy_boy 6y agoThis is not specifically denormalization because I'm not combining/flattening two models - instead, I'm moving the special treatment for employees from a totally separate path to a property of the existing customer model without removing/merging the employee model. Regarding read optimizations, there are several ways, some of which are vendor specific e.g. PostgreSQL/Oracle allow materialized views which can make accessing this data simple (if often required though they have their own issues).
- deleted 6y ago[deleted]