Подсчет записей в зависимости от внешнего ключа

У меня есть 2 таблицы, назовем их T_FATHER и T_CHILD, где у каждого отца может быть несколько дочерних элементов, например:

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

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

Теперь я хочу добавить счетчик в таблицу T_CHILD, который начинается с 1 и добавляет 1 для каждого нового ребенка, но не глобально, а для каждого отца, например:

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

Моя первоначальная мысль заключалась в создании триггера перед вставкой, который подсчитывает, сколько дочерних элементов присутствует для данного отца, и добавляет 1 для счетчика. Это должно работать нормально, если только нет двух вставок одновременно, которые заканчиваются одним и тем же счетчиком. Высоки шансы, что на самом деле этого никогда не произойдет, но лучше сэкономить, чем потом сожалеть.

Я не знаю, можно ли использовать генератор, но не думаю, что на каждого отца должен быть генератор.

Мой текущий подход заключается в использовании вышеупомянутого триггера и добавлении уникального индекса к FATHER_ID + COUNTER, чтобы выполнялась только одна из одновременных вставок. Мне придется обработать исключение на стороне клиента (и повторить попытку неудачной вставки).

Есть ли лучший способ справиться с этим непосредственно в Firebird?

PS: ни в одной из двух таблиц не будет никаких удалений, так что это не проблема.


firebird firebird-3.0
person Sebastian Proske    schedule 13.12.2016    source источник

Ответы (3)


arrow_upward
1
arrow_downward

А когда вы попробуете с вычисляемым полем и решением Select от 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)
  )
);
person FlixLux    schedule 15.12.2016

arrow_upward
2
arrow_downward

Даже с генератором на FATHER_ID вы не можете использовать их для этого, потому что генераторы не безопасны для транзакций. Если ваша транзакция по какой-либо причине откатывается, генератор все равно будет увеличиваться, вызывая пробелы.

Если нет удалений, я думаю, что ваш подход с уникальным ограничением действителен. Однако я бы рассмотрел альтернативу.

Вы можете решить не хранить счетчик как таковой — хранение счетчиков в базе данных часто является плохой идеей. Вместо этого отслеживайте только порядок размещения. Для этого можно использовать генератор, поскольку каждая новая запись будет иметь более высокое значение, а пробелы не будут иметь значения. На самом деле, вам не нужно ничего, кроме ID, которое у вас уже есть. Определить нумерацию при выборе; для каждого ребенка вы хотите знать, сколько детей есть с тем же отцом, но меньшим ID. В качестве бонуса удаление будет работать нормально.

Вот доказательство концепции с использованием вложенного запроса:

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

Есть также вариант оконной функции. У него должна быть производная таблица, чтобы также подсчитывать любые строки, которые в конечном итоге не выбираются:

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

Эти два варианта имеют совершенно разные тактико-технические характеристики. Какой из них подходит вам, зависит от размера ваших данных и шаблонов доступа.

person Thijs van Dien    schedule 13.12.2016
comment
Спасибо за ваш ответ. Я бы предпочел иметь счетчик в виде поля, поэтому я думаю, что на данный момент я буду придерживаться своей текущей попытки. Однако ваш альтернативный подход определенно интересен и может быть полезен для подобных проблем в будущем :) - person Sebastian Proske; 14.12.2016

arrow_upward
0
arrow_downward

Во время вставки вы должны просто сделать «Выбрать... количество + 1» непосредственно внутри этого поля.

Но я бы, наверное, еще раз подумал о добавлении этого поля. Это похоже на избыточную информацию, которую можно легко вывести в тот момент, когда она вам нужна. ://www.firebirdfaq.org/faq343/)

person stack reader    schedule 21.12.2016