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