How should I best structure my app when I want to sort a "parent" model by a property computed on another "child" model?

Question

Question about OO-programming, functional programming, NoSQL, SQL databases, and software architecture

Hi all,

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

For example:

- 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:

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

2. Use an object-oriented programming style to maintain these summary / computed values, saving them on the 'parent' model.

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

4. 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!

Answer 1

This question is very broad and there is no one-size-fits-all solution. I nevertheless go through the topics to help you to better narrow down the problem.

Let's first have a look at the data model:

• Your data model is about two different entities, Routes and Services, with a one-to-many relation between them. I also understand that a Service is "owned" by a single Route, i.e. if a Route would disappear, so would do all the corresponding Services.
• Some properties of these entities are independent. Typically a Route would have a unique ID, Created time, Assigned driver and Notes. In the same way, a Service would certainly have some kind of identifier that would make it unique within a Route, as well as Notes, isCompleted and isReconciled
• The property RouteId of a Service is just the implementation of the one-to-many link, and allow to go back from the many to the one.
• Some Route properties appear not to be independent: isCompleted, isReconciled and serviceCount. Here you have the choice between calculating them on the flow or updating them in function of other operations.

Here some thoughts about choosing the database type:

• If you often to combine Routes and Services as well as other entities (e.g. Driver and Vehicle), but also want to access to these entities independently, the SQL database could be a good choice. It's the strength of RDBMS. You could also do this with MongoDb using references, but combining related data would always be two steps.

• If on the other side Services are almost always processed in relation with Routes , and if the use of Routes frequently needs the corresponding Services, then a document-oriented database like MongoDB could be a nice alternative: you would store the Service documents embedded in the Route documents. Every time you read a Route, you'd have all the Services at hand.

• If services have also relations between them (e.g. what's the next service), or if the service are related to geographic points and you want to know all the services or routes that cross this point, then some other NoSQL databases like a more specialized graph database could make the things easier.

Now calculating the dependent properties or not ?

• In all these scenarios, you could perflectly calculate the dependent properties on the flow. SQL and MongoDB both have aggregate functions that help.
• If however you frequently use isComplete in queries, and especially if you want to combine conditions on isComplete with other conditions, it might be more cumbersome to use aggregates. A denormalized data schema that stores this information could ease the things.
• Furthermore, are you sure that the rule for isComplete will stay like that ? Isn't it possible that one day, the rule will evolve for example to ensure that Route isComplete only if all its Services are completed and a driver is assigned ? so it might not be so practical to have the on-the flow calculation done in a lot of queries. This speaks for using a method in an OO model: you can start to have the method to make the on-the-flow calculation; but you're free to change the logic and go for reading a stored value,n without changing the code that uses the proper interface.

With your information, I cannot objectively recommend any of those above. You have to consider each of these topic and see the options that are the more suitable for your case.

Answer 2

Being that these models describe a real world, physical action, it has some intinsic constraints. For instance, a driver is a human (as if 2019). A human can only drive for so long (both legally and physically). Even when the robots take our truck driving jobs a vehicle can only run for so long without refueling or breaking down. The vehicle can only carry so much weight.

Knowing the constraints of the business domain can lead you to a reasonable technical solution.

From these constraints I can gather that the dataset for each route is likely to be pretty small, on the order of a dozen or so records or objects. Due to the small size of the data for each route, using a functional or object-oriented language, and calculating the values each time you need them should be fine. Unless you can measure a legitimate performance problem (not just a difference, but a difference that is noticable to a user) then trying to precalculate these values to save time in code or a SQL database would be premature optimization.

Starting out, keep it easy on yourself. Add precalculations in the future if necessary, but I wouldn't start out there with this particular problem.

A summary of data storage technologies is included in Christophe's answer. Whether you go with a SQL database, NoSQL solution or graph database is a matter of chosing which one performs the best and can scale the best for your particular problem domain. Remember that data storage technologies specialize in storage and not behavior. Functional and OO languages specialize in behavior. Let that drive your decision.

Regardless of storage tech, things like isCompleted feel like they are behavior and are best kept in code, not data storage.

Answer 3

Well, you are speaking in terms of a domain, referring to objects, and writing an application. You are already talking OOP, in a sense. Think about the following:

• Databases are concrete implementations and not abstractions. You have to specify everything. Even choosing among database storage types is a hard choice that you have to live with (well... to an extent, maybe, but just for the sake of making a point here).

When dealing with domains, as you are (think Route, Service... these represent domain objects), it helps if you don't really worry about the storage while "designing". Imagine...Object-Oriented Programming language users did face a worry similar to yours:

• How do I cause a side effect when something takes place? Well... Observer pattern (think "events").

So

The globally accepted answer to your problem is: Think in term of abstractions, not implementations. Do not expose implementations. Strive to expose abstractions. Focus on one implementation at a time.

In simpler words, if you give a database to someone, with the promise to update its content regularly, you have to also supply them with your entire schema, so that they can query it to get whatever they wish. Because you can't change the schema, as might break older queries, you have to take a bunch of difficult decisions right at the beginning, and you only have one try. Think about:

• Performance
• Ease-of-use
• Expressive power
• Proper modelling of the domain (i.e. correctly capture and encode all relations and their characteristics).

You definitely don't want to get only one try! So the short answer is

• Decouple your abstractions from your implementations (code is an abstraction, database is an implementation). Write Object-Oriented code!

• Write some code that actually appears to do what you would like it to do (i.e. expresses intent) and bind this to your specific implementation, whatever it is you end up choosing anyway.

If you have code like: service.isCompleted or service.getIsCompleted(), then the rest of the code does not really care about how you are calculating this "behind" the getter. On-the-fly, or constant (stored), or any other way you can come up with.

• Worry about performance when time comes, not ahead of time. Excessive focus on performance hurts maintainability.

When the time comes, you will not have to change much of your code. Only the part which connects to the database and fetches the data. So, you can replace the old database with one that stores the parent properties, instead of calculating them on the fly (because, maybe you decide that many round-trips to the database are taking time), and the property itself will never appear any different. It's still just a bool value!