Моделирование данных с уровнями детализации, некоторые из которых отсутствуют

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

Проблема в том, что в некоторых случаях у меня будут только общие данные; У меня может быть «пенальти за матч» для одного матча и «пенальти за период» для другого. Таким образом, на самом низком уровне для некоторых матчей у меня будут очень подробные данные (штрафы за ход), а на самом высоком уровне у меня будут штрафы за матч.

Я не уверен, как смоделировать/использовать это для создания отчетов, когда у меня нет высокой детализации для некоторых записей. Я думал о чем-то вроде этого:

PenaltiesPerMatch MatchID PenaltyCount

PenaltiesPerPeriod MatchID PeriodID PenaltyCount

PenaltiesPerLap MatchID PeriodID LapID PenaltyCount

Но меня беспокоит то, что информация более высокого уровня может быть получена из более низкого уровня. Должен ли я дублировать записи (например, заполнять запись о штрафах за период данными, которые также содержатся в штрафах за круг, суммированных по периодам?) или вести уникальные записи (не вводить штрафы за период для данных, которые у меня уже есть в штрафах? за круг; рассчитайте путем суммирования по периоду).


sql datamodel
person Caveatrob    schedule 20.05.2009    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Я бы сделал запись информации, которая у вас есть. Для одних матчей записывайте с высокой детализацией, для других — с низкой.

Когда вы сообщаете о матчах:

  • Рассчитать суммы за совпадение для совпадений с высокой детализацией
  • Используйте сумму за совпадение из совпадений с низкой детализацией

Храните данные на самом низком уровне детализации, который у вас есть; рассчитать более высокие уровни детализации.

person Andomar    schedule 20.05.2009
comment
как бы вы справились с ситуацией, когда есть только данные высокого уровня? В этом случае расчет работать не будет. - person Aaron M; 20.05.2009
comment
Расчет превращает высокодетализированные (за квартал) записи в низкодетализированные (за матч) записи. Так что, если все в низкой детализации, нет необходимости в расчете. - person Andomar; 21.05.2009
comment
Да, но в ОП говорится, что в некоторых случаях у него могут не быть данных низкого уровня, а только данные высокого уровня. Если у него не всегда есть данные самого низкого уровня, он не может вычислить данные высокого уровня. - person Aaron M; 21.05.2009
comment
Для каждого совпадения у вас есть данные высокого или низкого уровня. Для первого типа соответствия у вас уже есть данные высокого уровня. Для второго типа соответствия вы можете вычислить данные высокого уровня из данных низкого уровня. Это довольно легко сделать в SQL. - person Andomar; 21.05.2009

arrow_upward
1
arrow_downward

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

Например:

CREATE TABLE PenaltyCounts
(
    penalty_count_id INT NOT NULL,
    match_id         INT NOT NULL,
    period           TINYINT NULL CHECK (period BETWEEN 1 AND 3),
    lap              SMALLINT NULL,
    penalty_count    SMALLINT NOT NULL,
    CONSTRAINT PK_PenaltyCounts PRIMARY KEY NONCLUSTERED (penalty_count_id),
    CONSTRAINT UI_PenaltyCounts UNIQUE CLUSTERED (match_id, period, lap),
    CONSTRAINT CK_lap_needs_period CHECK (lap IS NULL OR period IS NOT NULL)
)

Одна проблема с этим, для которой я пока не вижу простого решения, заключается в том, как обеспечить, чтобы они могли вводить штрафы ТОЛЬКО на одном уровне. Например, они все еще могли бы сделать это:

INSERT INTO PenaltyCounts (penalty_count_id, match_id, period, lap, penalty_count)
VALUES (1, 1, NULL, NULL, 5)
INSERT INTO PenaltyCounts (penalty_count_id, match_id, period, lap, penalty_count)
VALUES (2, 1, 1, NULL, 3)
INSERT INTO PenaltyCounts (penalty_count_id, match_id, period, lap, penalty_count)
VALUES (3, 1, 2, NULL, 2)

Преимущество этого решения с одной таблицей заключается в том, что всю вашу статистику можно найти, запросив одну таблицу, а GROUP BY все прекрасно свернет.

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

person Tom H    schedule 20.05.2009

arrow_upward
0
arrow_downward

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

Если у вас всегда был штраф по информации о периоде, тогда вы могли бы выполнить запрос, который суммирует данные.

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

person Aaron M    schedule 20.05.2009