Исторические справочники

← →
Bjdob © (2016-02-14 19:25) [0]

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

В результате всех размышлений пришел к такой таблице, в которой можно было бы хранить все справочники:

OBJECT_LIST_PARAM --------------------------- TYPE_OBJECT ID_OBJECT DT_BEGIN DT_END PARAM_NAME PARAM_VALUE_STRING PARAM_VALUE_INT ...

Фактически, каждая запись - это значение колонки в конкретной записи конкретного справочника.

TYPE_OBJECT - тип справочника, к которому относится запись. Например TYPE_OBJECT = 1 - справочник клиентов
ID_OBJECT - ID"шка записи в контексте TYPE_OBJECT"а
DT_BEGIN - начало действия записи
DT_END - конец действия записи
PARAM_NAME - название поля
PARAM_VALUE_STRING - значение поля, если оно типа string
PARAM_VALUE_INT - значение поля, если оно типа integer
и т.д.

Какие могут быть подводные грабли, недостатки, альтернативы данному подходу?

← →
Юрий Зотов © (2016-02-15 00:18) [1]

Первичный ключ TYPE_OBJECT, ID_OBJECT, DT_BEGIN ?

Если да, то получится, что объект можно изменять только один раз в день. Вряд ли это хорошо. И даже включение миллисекунд в DT_BEGIN не гарантирует уникальность на все 100%.

Таблицы с историей обычно делаются так: в первичный ключ включается только одно поле и добавляется еще одно поле - ссылка на первичный ключ предыдущей записи.

Либо в первичный ключ добавляется поле-счетчик (вместо DT_BEGIN).

← →
DayGaykin © (2016-02-15 00:36) [2]

Нормальный подход, не переживай.
Измени типы полей DT_BEGIN и DT_END на Timestamp или подобный.
Если пользователи умудрятся в одну миллисекунду изменить справочник - не страшно. Слишком маловероятно и совсем не опасно, чтобы заморачиваться.

Ну и я бы отселил прошлые значения в отдельную таблицу, чтобы не мешались.

← →
Юрий Зотов © (2016-02-15 00:39) [3]

> DayGaykin © (15.02.16 00:36) [2]

> Если пользователи умудрятся в одну миллисекунду изменить
> справочник

Справочник может менять и программа. А для нее 1 мс - это немало.

← →
Kerk © (2016-02-15 00:59) [4]

> Таблицы с историей обычно делаются так: в первичный ключ
> включается только одно поле и добавляется еще одно поле
> - ссылка на первичный ключ предыдущей записи.

Голосую за этот вариант. Добавить поле с датой создания записи и будет совсем хорошо :)

← →
DayGaykin © (2016-02-15 01:06) [5]

Предположим что это справочник цен и нужно пересчитать стоимость некой корзины на какое-то число. Как вы будете доставать цены?

← →
Юрий Зотов © (2016-02-15 01:38) [6]

По поводу дат и миллисекунд:
https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/current_timestamp?lang=ru

Как доставать цены: как обычно, по попаданию в диапазон дат (с учетом того, что в актуальной записи DT_END=NULL).

← →
Кщд © (2016-02-15 06:34) [7]

>Bjdob © (14.02.16 19:25)
как обычно: прежде, чем писать, нужно читать
напр., про slowly changing dimensions
точнее: SCD type 2

← →
Sergey13 © (2016-02-15 08:55) [8]

> PARAM_VALUE_STRING - значение поля, если оно типа string
> PARAM_VALUE_INT - значение поля, если оно типа integer

Тогда уж правильнее, ИМХО, будет
TYPE_VALUE - тип значения
PARAM_VALUE_STRING - значение поля, приведенное к string

← →
DayGaykin © (2016-02-15 09:22) [9]

> Юрий Зотов © (15.02.16 01:38) [6]
> По поводу дат и миллисекунд:
> https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/current_timestamp?
> lang=ru
>
> Как доставать цены: как обычно, по попаданию в диапазон
> дат (с учетом того, что в актуальной записи DT_END=NULL).
>

Я понял, вы предлагаете и ID предыдущей записи хранить и даты.
П.С. ссылка не открывается :(

← →
DayGaykin © (2016-02-15 09:22) [10]

← →
Юрий Зотов © (2016-02-15 09:45) [11]

> DayGaykin © (15.02.16 09:22) [10]

Даты надо иметь обязательно, иначе смысл истории теряется. А ссылка на предыдущую запись эту историю создает.

Ссылка... хм, действительно. А вчера открывалась. Ну да ладно, можно и другие нагуглить.

Речь в статье по ссылке идет о неуникальности current_timestamp. Современные сервера шустрые, за 1 мс много всего успевают.

На эту неуникальность я натыкался лично. С тех пор поля даты/времени в ключах и индексах не использую и другим не советую.

← →
Павел Калугин © (2016-02-15 11:19) [12]

> Юрий Зотов © (15.02.16 00:18) [1]
> Первичный ключ TYPE_OBJECT, ID_OBJECT, DT_BEGIN ?Если да,
> то получится, что объект можно изменять только один раз
> в день. Вряд ли это хорошо. И даже включение миллисекунд
> в DT_BEGIN не гарантирует уникальность на все 100%.Таблицы
> с историей обычно делаются так: в первичный ключ включается
> только одно поле и добавляется еще одно поле - ссылка на
> первичный ключ предыдущей записи.Либо в первичный ключ добавляется
> поле-счетчик (вместо DT_BEGIN).

Решение со ссылкой на "предыдущую" запись интересное и имеет много возможностей, но тоже имеет свои грабли.
А и Б правят запись с ID = X понятно, что кто последний, тот и папа. Но ссылка получается у двух записей журнала на одного "папу"
Можно, конечно, обвесить вычислением "папы" само сохранение Но тоже грабли
При запрашиваемой интенсивности сохранения журнала, когда милисекунда играет роль во первых нужен подход уже как для высоконагруженных систем а это:
Транзакция А пишет данные
Транзакция Б пишет данные
Далее варианты
- Б ждет завершения А - лишние тормоза в высоконагруженной системе
- Б поучает "грязные" данные (ссылку на запись сформированную А), но транзакция А откатывается, а Б успешно сохраняется
- как описано выше Б и А вычисляют одну и туже сохраненную запись в результате или нарушено условие ключа или транзакция Б падает с ошибкой "нарушение ключа"
- и совсем вырожденный случай Б и А блокируют друг друга, система встает в ступор

← →
Сергей Суровцев © (2016-02-15 15:30) [13]

А зачем в данном контексте ID предыдущей записи?
Для реализации истории в отдельном справочнике хватит 3х полей:
ID - сквозной.
ID_GR - номер для группы (группа-логическая единица, объект справочника).
DT_BEGIN - дата, с которой данная версия логической единицы актуальна.
Далее любые поля информации.

DT_END необязательна, даже вредна.
ID предыдущей записи ничего не дает.

Если речь идет о том чтобы делать срез ВСЕХ справочников на дату, чтобы хранить историю их изменений, то это уже другой подход.

← →
DayGaykin © (2016-02-15 15:48) [14]

> DT_END необязательна, даже вредна.

Почему, давайте разберемся?
Без нее выборка записей за определенную дату нетривиальна.

← →
Сергей Суровцев © (2016-02-15 16:02) [15]

>DayGaykin © (15.02.16 15:48) [14]
>Без нее выборка записей за определенную дату нетривиальна.

Почему? Делаем запрос по дате с сортировкой. Последняя строка актуальна. Если внутри одной даты, то еще и время. Смысл в том, что на один конкретный момент не может быть двух актуальных строк, только одна. Следующая строка отменяет предыдущую автоматом, самим фактом своего наличия. Зачем же лезть в предыдущую чтобы поставить ей DT_END?

← →
Павел Калугин © (2016-02-15 17:00) [16]

Сергей Суровцев, тут малость о другом речь. Если в один момент времени применено 2 правки в истории надо сохранить обе.

← →
DayGaykin © (2016-02-15 17:34) [17]

> Почему? Делаем запрос по дате с сортировкой. Последняя строка
> актуальна. Если внутри одной даты, то еще и время. Смысл
> в том, что на один конкретный момент не может быть двух
> актуальных строк, только одна. Следующая строка отменяет
> предыдущую автоматом, самим фактом своего наличия. Зачем
> же лезть в предыдущую чтобы поставить ей DT_END?

Как быть, если нужно выбрать несколько записей справочника за эту дату?
Ну или просто приJOIN-ить справочник к другой таблице?

Поле DT_END - нужно.

← →
Игорь Шевченко © (2016-02-15 18:22) [18]

А если кто-то поделится способом атомарной проверки на пересечение периода добавляемой записи с имеющимися в таблице, буду признателен.

← →
Сергей Суровцев © (2016-02-15 18:24) [19]

>Павел Калугин © (15.02.16 17:00) [16]
>тут малость о другом речь. Если в один момент времени применено 2 правки в истории надо сохранить обе.

Прекрасно. А что мешает? ID у них будут разные. Дата-время одинаковые? Значит актуальная с большим ID.

>Юрий Зотов © (15.02.16 09:45) [11]
>С тех пор поля даты/времени в ключах и индексах не использую и другим не советую.

Абсолютно справедливо.

>DayGaykin © (15.02.16 17:34) [17]
>Как быть, если нужно выбрать несколько записей справочника за эту дату?

Разных записей одной группы или разных?
Условие на дату <= + максимальный ID даст строго 1 актуальную строку.

>Ну или просто приJOIN-ить справочник к другой таблице?

То же самое.

>Поле DT_END - нужно.

Зачем?

Хорошо бы реальный пример, а то много теоретизирования и вариантов получается.

← →
Сергей Суровцев © (2016-02-15 18:42) [20]

>DayGaykin © (15.02.16 17:34) [17]
>Поле DT_END - нужно.

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

← →
DayGaykin © (2016-02-15 18:52) [21]

> Сергей Суровцев © (15.02.16 18:24) [19]

Я недавно сталкивался с такой задачей и ввел два поля в итоге.
Если предложите хорошее решение, буду рад, поэтому предлагаю построить SQL запросы для следующих задач:
street: id | id_gr | dt_begin | name 1 | 100 | 2015-02-15 | М. Горького 2 | 100 | 2015-04-18 | М. А. Горького 3 | 100 | 2015-07-01 | Максима Горького 4 | 200 | 2015-01-15 | Ленина 5 | 200 | 2015-09-04 | Вождя 6 | 200 | 2015-12-10 | Путина client: id | name | visit_date | street_gr_id 1 | Владимир | 2015-05-23 | 100 2 | Сергей | 2015-09-11 | 100 3 | Иван | 2015-11-13 | 200 4 | Инна | 2015-12-11 | 200 Задача 1: Получить весь справочник улиц сгруппированный по id_gr на дату: а) 2015-06-11: id_gr | name 100 | М. А. Горького 200 | Ленина б) 2015-01-20: id_gr | name 200 | Ленина Задача 2: Вывести посетителей с названием улиц на дату визита: id | name | visit_date | street_name 1 | Владимир | 2015-05-23 | М. А. Горького 2 | Сергей | 2015-09-11 | Максима Горького 3 | Иван | 2015-11-13 | Вождя 4 | Инна | 2015-12-11 | Путина

← →
TohaNik © (2016-02-15 21:14) [22]

Согласен с
> Сергей Суровцев © (15.02.16 15:30) [13]
,
в общем и целом.
Один момент лечится ДАМПом.:)

← →
Bjdob © (2016-02-15 22:26) [23]

> Первичный ключ TYPE_OBJECT, ID_OBJECT, DT_BEGIN ?

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

OBJECT_LIST_PARAM --------------------------- ID TYPE_OBJECT ID_OBJECT DT_BEGIN DT_END PARAM_NAME PARAM_VALUE_STRING PARAM_VALUE_INT ...

> А ссылка на предыдущую запись эту историю создает.

не понимаю, зачем нужна ссылка на предыдущую запись?
Ведь все исторические записи в рамках одного объекта будут иметь одинаковые TYPE_OBJECT, ID_OBJECT.

> Тогда уж правильнее, ИМХО, будет
> TYPE_VALUE - тип значения
> PARAM_VALUE_STRING - значение поля, приведенное к string

и тут же теряется половина возможностей БД. Например, применение агрегатных функций.

> DT_END необязательна, даже вредна.

в принципе, необязательна. Хронология может определяться или DT_BEGIN, или DT_END.
Но я не представляю как делать JOIN"ы. При наличии как DT_BEGIN, так и DT_END вытащить нужные запись на некий срез времени dt легко, всего два условия добавить к стандартному join"у:

... join on primary.id = detail.primary_id and primary.dt_begin <= dt and primary.dt_end >= dt

При отсутствии DT_END такой огород придется городить...

← →
Bjdob © (2016-02-15 22:32) [24]

> А если кто-то поделится способом атомарной проверки на
> пересечение периода добавляемой записи с имеющимися в таблице,
> буду признателен.

использовать, например, только dt_begin? И на dt_begin навесить уникальный ключ в паре с type_object, id_object.

То есть, действие одной записи заканчивается там, где начинается dt_begin следующей записи. Тогда нет явного диапазона и не нужно следить за пересечением диапазонов. Историю править проще.

Но вот выборка данных, имхо, будет гораздо сложнее. Чтобы определить конец действия записи нужно найти следующую запись с минимальным dt_begin больше твоего.

← →
Bjdob © (2016-02-15 22:38) [25]

Вообще, странно, что народ сказал подход нормальный. В этой таблице:

> OBJECT_LIST_PARAM
> ---------------------------
> ID
> TYPE_OBJECT
> ID_OBJECT
> DT_BEGIN
> DT_END
> PARAM_NAME
> PARAM_VALUE_STRING
> PARAM_VALUE_INT
> ...

Каждая запись хранит ОДНО ПОЛЕ объекта. Минусы такого, например:

1) со стороны БД нет контроля целостности записи. Ну например поля not null. Default поля и так далее.

2) сложно делать те же join"ы, потому что ты получишь не связку объектов, а связку полей. Допустим, у объекта 7 полей, тогда ты получишь 7 записей при джойне на один объект, потому что:

> TYPE_OBJECT
> ID_OBJECT
> DT_BEGIN
> DT_END

будут одинаковыми для каждого поля.

← →
Юрий Зотов © (2016-02-15 22:52) [26]

> Bjdob © (15.02.16 22:38) [25]

> странно, что народ сказал подход нормальный.

Как Вы описали топик (в котором речь шла только об одной таблице и больше никакой информации не было ) - так народ и сказал.

Если бы Вы описали не таблицу, а суть всей задачи, то народ и сказал бы иначе.

← →
Юрий Зотов © (2016-02-15 22:55) [27]

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

← →
Bjdob © (2016-02-15 22:55) [28]

> точнее: SCD type 2

я читал на русском: http://www.prj-exp.ru/dwh/slowly_changing_dimension.php

Не понял - зачем для SCD Type 2 нужны поля:

IS_ACT_IND – индикатор активной записи: 1 – активна; 0 – не активна; IS_DEL_IND – индикатор удаленной записи: 1 – удалена; 0 – не удалена.

Если тебе нужны актуальные данные - ты просто поднимаешь записи на текущий sysdate. Типа:

... and t1.IS_ACT_IND = 1

выполняется быстрее, чем:

... and t1.dt_begin <= sysdate and t1.dt_end >= sysdate

В этом логика?
Ведь так то это денормализация, вдруг IS_ACT_IND = 1 у одной записи, а по диапазону в sysdate находится другая запись?

Тоже с IS_DEL_IND, зачем он нужен, если "удаленная" запись, это просто те, у которых max(dt_end) < sysdate?
Так даже удобнее, например я могу назначить dt_end на завтра в 14:00, и с этого момента она будет считаться удаленной.

Не знаю сути задачи, могу только догадываться по названиям полей. И если исходить из этих названий, то я бы сделал примерно так (и реально сделал, была у меня лет 8 назад подобная задачка - и за эти 8 лет пересматривать ничего не пришлось). Привожу по памяти, возможны неточности.
object_types: id(pk), name, .... objects: id(pk), name, object_type_id(fk), ... prop_types: id(pk), name, real_type, ... object_props: id(pk), name, object_id(fk), prop_type_id(fk), str_value, ... history: id(pk), prev_id, object_prop_id(fk), dateFrom, dateTo, ...

Таблица object_props реализует связь "многие ко многим" между таблицами objects и prop_types - то есть, поля object_id и prop_type_id тоже должны составлять ключ.

>DayGaykin © (15.02.16 18:52) [21]
>Если предложите хорошее решение, буду рад, поэтому предлагаю построить SQL запросы для следующих задач:

Задача 1:

SELECT t1.id_gr, t2.id, t2.name FROM
(
SELECT id_gr, Max(id) as mid FROM street AS s1
WHERE dt_begin <= "11.06.2015"
GROUP BY id_gr) as t1
LEFT JOIN street as t2 ON t1.mid=t2.id

Задача 2:

SELECT n1.id, n1.name, n1.visit_date,

(SELECT t2.name
FROM
(SELECT id_gr, Max(id) as mid FROM street AS s1
WHERE dt_begin <= n1.visit_date AND id_gr = n1.street_gr_id
GROUP BY id_gr) as t1
LEFT JOIN street as t2 ON t1.mid=t2.id
) as street_name

FROM client as n1

>Bjdob © (15.02.16 22:55) [28]
>выполняется быстрее, чем:
>В этом логика?
да
обычно в таких системах отношение кол-ва записей с IS_ACT_IND = 1 к кол-ву с IS_ACT_IND = 0 стремится к нулю
следовательно, выборка по индексу на поле IS_ACT_IND (= 1) крайне эффективна

>Сергей Суровцев © (16.02.16 02:23) [32]
SELECT id_gr, Max(id) as mid FROM street AS s1
логически неверно
нужно получить ID с максимальной датой, а не максимальный ID
в общем случае, порядок ID - вовсе не эквивалентен хронологическому

>Кщд © (16.02.16 08:31) [34]
>логически неверно

Это и есть ID с максимальной датой. ID заполняется линейно. Как и история справочника. Ориентироваться на ID, заполняемый по рандому не имеет смысла.

2Bjdob © (15.02.16 23:06) [29]

> А вот пример данных:
> 1 | 1 | 01.01.1970 | 01.01.2099 | "COST" | null | 56
> 1 | 1 | 01.01.1970 | 01.01.2099 | "NAME" | "Мармелад" |
> null

> и тут же теряется половина возможностей БД. Например, применение
> агрегатных функций.

И какие агрегаты на таких данных нужны?
Какова вообще ценность таких данных? Что с ними предполагается делать - просто распечатать столбиком или как-то еще обрабатывать? Если второе, то как при выборке понять, что "COST" это именно цена, а не вес или количество? Анализировать PARAM_NAME? А там "CENA", "ЦЕНА", "ZEHA" и еще варианты с русскими и аглицкими буквами вперемешку.

ЗЫ: копейки в РФ еще не отменены кстати. Поэтому INT - спорный, как минимум, выбор. А если нужно будет хранить еще и дату годности того мармелада?

CREATE TABLE object_1 ( id ... property_1 ... property_2 ... property_3 ... ); CREATE TABLE object_1_history ( changing_id ... object_id ... -- По желанию, либо: changed ... -- либо: from_date ... till_date ... -- Дальше значения свойств до изменения: property_1 ... property_2 ... property_3 ... );

Сергей Суровцев © (16.02.16 08:51) [35]
>ID заполняется линейно.
ID, если речь о sequence, generator и пр. - функция возрастающая, это факт
1. вот только физически запись с меньшим ID может быть закоммичена позже записи с большим ID
2. ID может быть цикличен
3. ID может не быть числом
поэтому Ваш SQL-запрос логически неверен

>обычно в таких системах отношение кол-ва
>записей с IS_ACT_IND = 1 к кол-ву с
>IS_ACT_IND = 0 стремится к нулю

ну у меня как раз корректировка записи редкое явление

Исторические справочники Найти похожие ветки