'SQL Association Count Best Practice

Imagine I have a Parent.hasMany(Child) relationship, if I have an API to query a Parent but also need to surface how many children this parent has, I have 2 immediate options:

  1. Run a query on COUNT(child.id) (I feel this must be very hard to scale as we add more and more children in for a given Parent
  2. Maybe have a n_count attribute defined on the Parent and do a SQL transaction to modify the count on the parent every time a Child is created/deleted

Which is the better option here, or is there a third and best way?

sqldatabaseapidatabase-designapi-design


Solution 1:[1]

Storing n_count in the parent is generally considered undesirable because it is redundant information (information which can be obtained more reliably by counting the child records). Having said that, if the updates to the parent and child rows (n_count) is controlled to guarantee correct updates (by database triggers, for example), then this can be called a type of 'controlled de-normalisation', and used for performance improvement (only improves read-queries, updates/inserts will be slower, of course).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions