Storing count of child rows in parent table

Question

I have two tables, parent and child. Parents can "have" multiple children—in our case they are related through a third table, parent_child_mapping rather than the child storing its parent id directly. (Legacy reasons.)

We now need a paginated list of parents, sortable by the count of children per parent.

Is this an appropriate use case for storing counts in the parent table? i.e.

parent.child_count

If so, what is the standard way to manage the integrity of such a count? My idea is to simply re-run the count any time a child is being added or removed to a parent, rather than incrementing / decrementing. Any other helpful patterns?

Answer 1

No, you don't. For the majority of databases, providing a simple count of child table entries should be a piece of cake compared to the effort of keeping a parent table entry count consistant with your own code in the parent table (If not, reconsider your choice of database, because that's exactly what databases are good at...).

When you start thinking about external entities changing your child entry count (like auto-expiring entries or database purges,...) this is bound to become a nightmare.

Answer 2

In the context of an R-DBMS the recommended practice is to avoid redundant information, as it is very easy to get out of sync.

Typically, you would use database normalization. Most related tutorials address redundant values in groups of columns. However the definition of normal forms are based on the concept of functional dependency, and aggregate functions on a group of rows are a special form of functional dependencies. You should therefore avoid to store this data that could be obtained with a GROUP BY clause.

The advantage of avoiding a COUNT field and use a SELECT ..., COUNT(*) FROM ... GROUP BY ... instead, is that the result would always be correct, even if a user would inject data into the DB with an application that is not aware of implicit consistency rules.

The inconvenience, is that this could make queries more complex or less performant. If you are in such a (rare) case, you could consider denormalization, but with extreme care:

• you could ensure that every application that updates the data would update the count. But this is error prone, especially if many applications are writing data. A single ill-behaving application could mess everything up.
• you could instead use database triggers to ensure consistency at the DB level, independently of the applications. The issue is that you'll be confronted with proprietary non-portable syntax.

In both case, you might suffer from unexpected transactional consequences, e.g. locking of parent records when adding a child. Normalization avoids such surprise side-effects.

Answer 3

The standard way to maintain integrity is to count the child rows as needed, even when ordering data (e.g. order by count(x)). Counting child rows can be accomplished with a separate SQL query (e.g. select count(*) from parent join parent_child_mapping ...) or a database view. Views make it convenient to order by the child count, because it would be a simple order by view_name.child_count in a SQL query.

Keeping a child count in the parent table makes your application vulnerable to race conditions between competing INSERT and DELETE statements affecting the parent_child_mapping table.

You can circumvent this by adding a trigger for all INSERTs and DELETEs on parent_child_mapping to update the corresponding child count on the parent table, but then you have "magic" behavior in the database. Triggers can be difficult to debug if you have data issues or performance problems. Furthermore, you are not able to conditionally execute a trigger. It might be desirable in some use cases to defer the child count calculation until later. This won't be possible when performing the calculation in triggers.

With proper indexes, getting row counts based on a primary or foreign key should be pretty quick. Consider creating a database view that "flattens" the normalized data to include child row counts for each parent. These counts will be calculated at the time a client queries against that view.

Start with a properly normalized database, which means you do not keep track of child record counts in the parent table. Use separate queries to calculate the number of children where appropriate to ensure accurate information. If you need to denormalize data, first start by creating views. Most performance problems can be solved by optimizing queries and adding indexes.

Flattening multiple tables into a single table should only be done when a performance problem has been detected and tracked down to calculating row counts, and all other mitigation techniques have failed.

If these child counts are only required to run reports, consider pushing data to a reporting database in whatever denormalized form is convenient. The normalized tables remain the source of truth so INSERTs, UPDATEs and DELETEs do not affect the accuracy of child counts.