Counting entries depending on foreign key

Question

I have 2 tables, let's call them T_FATHER and T_CHILD, where each father can have multiple childs, like so:

T_FATHER
--------------------------
ID - BIGINT, from Generator

T_CHILD
-------------------------------
ID - BIGINT, from Generator
FATHER_ID - BIGINT, Foreign Key

Now I want to add a counter to the T_CHILD table, that starts with 1 and adds 1 for every new child, but not globally, but per father, like:

ID | FATHER_ID | COUNTER |
--------------------------
1  | 1          | 1      |
--------------------------
2  | 1          | 2      |
--------------------------
3  | 2          | 1      |
--------------------------

My initial thought was creating a before-insert-trigger that counts how many childs are present for the given father and add 1 for the counter. This should work fine unless there are 2 inserts at the same time, which would end with the same counter. Chances are high that this never actually happens - but better save than sorry.

I don't know if it is possible to use a generator, but don't think so as there would have to be a generator per father.

My current approach is using the aforementioned trigger and add a unique index to FATHER_ID + COUNTER, so that only one of the simultaneous inserts goes through. I will have to handle the exception client-side (and reattempt the failed insert).

Is there a better way to handle this directly in Firebird?

PS: There won't be any deletes on any of the two tables, so this is not an issue.

Answer 1

Even with a generator per FATHER_ID you couldn't use them for this, because generators are not transaction safe. If your transaction is rolled back for whatever reason, the generator will have increased anyway, causing gaps.

If there are no deletes, I think your approach with a unique constraint is valid. I would consider an alternative however.

You could decide not to store the counter as such – storing counters in a database is often a bad idea. Instead, only track the insertion order. For that, a generator is usable, because every new record will have a higher value and gaps won't matter. In fact, you don't need anything but the ID you already have. Determine the numbering when selecting; for every child you want to know how many children there are with the same father but a lower ID. As a bonus, deletes would work normally.

Here's a proof of concept using a nested query:

SELECT ID, FATHER_ID,
       (SELECT 1 + COUNT(*)
        FROM T_CHILD AS OTHERS
        WHERE OTHERS.FATHER_ID = C.FATHER_ID
          AND OTHERS.ID < C.ID) AS COUNTER
FROM T_CHILD AS C

There's also the option of a window function. It has to have a derived table to also count any rows that are ultimately not being selected:

SELECT * FROM (
  SELECT ID, FATHER_ID, 
         ROW_NUMBER() OVER(PARTITION BY FATHER_ID ORDER BY ID) AS COUNTER
  FROM T_CHILD
  -- Filtering that wouldn't affect COUNTER (e.g. WHERE FATHER_ID ... AND ID < ...)
)
-- Filtering that would affect COUNTER (e.g. WHERE ID > ...)

These two options have completely different performance characteristics. Which one, if either at all, is suitable for you depends on your data size and access patterns.

Answer 2

And when you try with a computed field and the Select solution of Thijs van Dien ?

CREATE TABLE T_CHILD(
  ID INTEGER,
  FATHER_ID INTEGER,
  COUNTER COMPUTED BY (
    (SELECT 1 + COUNT(*)
        FROM T_CHILD AS OTHERS
        WHERE OTHERS.FATHER_ID = T_CHILD.FATHER_ID
          AND OTHERS.ID < T_CHILD.ID)
  )
);

Answer 3

During the insert, you should just do a "Select...count + 1" directly inside that field.

But I would probably reconsider adding that field in the first place. It feels like redundant information that could easily be deduced at the moment you need it.(For example, by using DENSE_RANK http://www.firebirdfaq.org/faq343/)