Question about OO-programming, functional programming, NoSQL, SQL databases, and software architecture
I am a intermediate-level self-taught programmer, and have been dying to get an answer to this general question about how to structure my apps. Having no CS degree and only a few programmer friends, I haven't been able to get a strong view on what is the "correct" or "better" way to be approaching this kind of problem.
This kind of pattern comes up heaps everywhere in my current app, and I'm not sure what I should be doing. The pattern is:
I have one database model which is a 'container' or 'parent' type of model, which has many items that refer to it. For the sake of this, we can use a 'Route' model, which represents a courier drivers route for the day.
It has fields like:
- Created time - Assigned driver - Notes - isCompleted - isReconciled - serviceCount
I have another database model which is the main 'child' that refers to the 'parent', in this case, the route. In this case it is a 'service' and it represents each of the stops the courier driver has to make as part of their route for the day. All services for the relevant route have a reference to the route. Relevant fields on a service are:
- Route <- id referring to the 'parent' model - Notes - isCompleted - isReconciled
In many of these cases, I have a situation where if you change a service (the child), you may want to update some 'top-level' or 'summary value' in the route (the parent).
- When all services are 'complete', update the route to be 'complete' - When all services are 'reconciled', update the route to be 'reconciled' - If you add a service to a route, update the 'service count' on the route
Currently, to handle this kind of thing, I have functions that normally run after every relevant mutation to a service. It would be called something like updateRouteAfterServiceIsModified. It simply updates the route after all modifications to services, to update these summary values.
I do however understand that if I adopted more of an object-oriented approach, I could have some kind of post-save hook, or something similar, such that if you ever modify a service, the route will be updated. I can see how this might make code organisation better, but can also see how it could get messier.
One of the things that I have thought of doing is to basically never compute these summary values when doing mutations to services at all, since they can be determined on-the-fly, as they are basically inferred or computable properties. For example:
- To know if the route is complete, just check if all the services are complete - To know if the route is reconciled, just check if all the services are reconciled - To know the route count, simply count the number of services, as at the time you need to know
The problem in computing these on-the-fly for me is that I will typically have some list of the 'parent' item (routes), and I will want to do something like sort by these computed fields. If the value is not saved on the parent, I have to compute the value on the fly to sort the list.
I am using Mongo as my datastore, and the aggregations on foreign fields get quite complicated quite quickly, and other relevant computations to perform the desired sort, and it seems like a solution that is overly-complex and possibly not scalable. But I am not a mongo expert, so I am not sure.
I have never used SQL e.g. PostgreSQL; I have one friend who has suggested that this kind of thing is much easier in Postgres. Perhaps it is possible that the "normal" thing to do in this situation with Postgres would never to save these summary values on the parent, and simply to compute them at sort time, and that Postgres does this efficiently. Perhaps it is the case that the code does not become messy, and that it scales well. If that is the case, although the app is mostly built, I would consider swapping to Postgres - but I have simply never tried.
In summary, I feel like there may be 4 potential solutions to this kind of problem:
Use a functional programming style, and appropriately wrap or call functions when doing 'child' mutations to save all the data you want on the relevant 'parent' model.
Use an object-oriented programming style to maintain these summary / computed values, saving them on the 'parent' model.
There is no need to save these values, compute them on the fly, Mongo is fine, aggregations can be very powerful, you just have to master them and build some abstractions where it would be helpful or simplify your code.
There is no need to save these values, and key to this is using SQL, which makes sorting by a foreign-field computed value easy, you should switch as it makes this kind of process trivial.
I'm not sure if there is a 'correct' approach to this, but it seems like such a common pattern in my experience that I would have thought there would be some consensus in the development community about what I should do.
Any help would be greatly appreciated, even if it is just directing me to resources that may be helpful!