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

← →
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, и с этого момента она будет считаться удаленной.

← →
Bjdob © (2016-02-15 23:06) [29]

← →
Юрий Зотов © (2016-02-16 00:57) [30]

Не знаю сути задачи, могу только догадываться по названиям полей. И если исходить из этих названий, то я бы сделал примерно так (и реально сделал, была у меня лет 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, ...

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

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

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

>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

← →
Кщд © (2016-02-16 08:27) [33]

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

← →
Кщд © (2016-02-16 08:31) [34]

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

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

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

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

← →
Sergey13 © (2016-02-16 09:01) [36]

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 - спорный, как минимум, выбор. А если нужно будет хранить еще и дату годности того мармелада?

← →
Владислав © (2016-02-16 09:43) [37]

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

← →
Кщд © (2016-02-16 09:45) [38]

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

← →
Владислав © (2016-02-16 09:48) [39]

> Игорь Шевченко © (15.02.16 18:22) [18]

Отложенный constraint + серверная блокировка подойдут?

← →
Bjdob © (2016-02-16 10:24) [40]

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

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

← →
Кщд © (2016-02-16 11:25) [41]

>Bjdob © (16.02.16 10:24) [40]
SLOWLY CHANGING dimension же)
если записи не изменяются, то и смысла городить огород нет
если всё же меняются, то рано или поздно "отношение стремится к нулю"
в общем, Ваша задача давно формализована, равно как и методы её решения - это чистый SCD
какой тип больше подходит Вам - решать только Вам

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

>Кщд © (16.02.16 09:45) [38]

Данный запрос написан под конкретные условия конкретной задачи. Будут другие условия, будет другое решение.

>1. вот только физически запись с меньшим ID может быть закоммичена позже записи с большим ID
Теоретически, если генерировать дату на клиенте, то да. Практически, если генерировать дату-время вместе с ID, то нет. Единственная возможность - изменить дату в уже внесенной записи, но тогда это уже не "справочник с историей".

>2. ID может быть цикличен
Тогда это уже не ID

>3. ID может не быть числом
А еще его может вообще не быть. Или быть, но не он. Или еще что-нибудь. Решать нужно конкретную задачу, а не выдумывать из нее теоретическую абстракцию на все случаи жизни.

>поэтому Ваш SQL-запрос логически неверен
Так предложите свой вариант.

← →
Кщд © (2016-02-16 12:12) [43]

>Сергей Суровцев © (16.02.16 11:42) [42]
>Данный запрос написан под конкретные условия конкретной задачи
зачем выдумывать велосипед, который будет работать при некоторых допущениях, когда можно написать то, что будет работать при любых?)

>Теоретически, если генерировать дату на клиенте, то да.
это не так
1. сессия №1 получает ID = 1;
2. сессия №2 получает ID = 2;
3. сессия №2 вставляет данные в таблицу и делает commit;
4. сессия №1 вставляет данные и делает commit.

>Тогда это уже не ID
если Вы ни разу не сталкивались с циклическими генераторами ID, значит, их не существует
я понял)

>Решать нужно конкретную задачу
решать нужно так, чтобы не выстрелить себе в ногу
если разница между устойчивым и неустойчивым решениями - одна строка кода, то выбор как бы очевиден

>Так предложите свой вариант.
уже предложил: необходимо выбирать ID, соответствующий максимальной дате
это приведет к тому, что в Ваш запрос добавится ещё один подзапрос

← →
Bjdob © (2016-02-16 12:54) [44]

>если всё же меняются, то рано или
>поздно "отношение стремится к нулю"

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

более того, на бесконечном времени отношение будет стремится к бесконечноси, а не к нулю

>Кщд © (16.02.16 12:12) [43]
>это приведет к тому, что в Ваш запрос добавится ещё один подзапрос

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

Хорошо, так академичнее?
Про commit. Неважно, кто будет первый, кто второй. Важно - когда они получат дату - вместе с ID или потом.
Суть вопроса была в том, чтобы показать, что DT_END для решения обозначенных задач не нужно. И таки да, ни в первом, ни во втором варианте их нет.

>Bjdob © (16.02.16 12:59) [45]
>более того, на бесконечном времени отношение будет стремится к >бесконечноси, а не к нулю
речь шла только о том, что для получения АКТУАЛЬНЫХ данных, сканирование по индексу на IS_ACT_IND будет эффективнее, чем по DT_BEGIN, DT_END

>Сергей Суровцев © (16.02.16 14:48) [46]
>Хорошо, так академичнее?
нет
одно лишнее и, к тому же, внешнее объединение
select s.* from street s where s.id in ( select max(s2.id) from street s2 where s2.dt_begin = (select max(s3.dt_begin) from street s3 where s3.id_gr = s2.id_gr and s3.dt_begin <= date "2015-06-11" ) group by s2.id_gr )

>Суть вопроса была в том, чтобы показать, что DT_END для решения >обозначенных задач не нужно
нужно по двум причинам:
1. запись может быть отключена на период, удалена
2. поиск актуальной записи на дату эффективнее по индексу, построенному на dt_begin, dt_end

Только что получил ситуацию, когда ADD_TIME(ID) перестала быть монотонно возрастающей:
У постгреса есть кеш при получении ID. ( http://www.postgresql.org/docs/8.2/static/sql-createsequence.html )
Этот кеш свой на каждое соединение. Поэтому ситуация, когда ID не коррелирует с ADD_TIME очень вероятная в случае более одного соединения с базой данных. Намного более вероятная, чем обратная.

>DayGaykin © (18.02.16 13:00) [49]
есть два простых правила:
1. никогда-никогда-никогда не использовать ID в реализации бизнес-логики
2. всегда-всегда-привсегда использовать ID по прямому назначению: ТОЛЬКО для уникальной идентификации записи

следуя этим правилам, избавляете себя и своих последователей от трудноуловимых ошибок и несете в мир радость и добро

>Кщд © (18.02.16 10:19) [48]
Так даже изящнее.

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

1) 01/01/2015 --- 05/01/2015
2) 05/01/2015 --- 10/01/2015
3) 10/01/2015 --- 15/01/2015

Удаляем (отключаем) (2), какую строку найдет поиск на 07/01/2015?
1) уже неактуальна.
3) еще не актуальна.

>2. поиск актуальной записи на дату эффективнее по индексу, построенному на dt_begin, dt_end

А индекс только на dt_begin будет менее эффективен?

> Кщд © (18.02.16 13:25) [50]

Это да! И никогда не соглашайтесь на "Этого ни когда не будет" если для выбора одной записи в ключе не хватает хотя бы одногоо поля:0

>Сергей Суровцев © (18.02.16 17:33) [51]
>Удаляем (отключаем) (2), какую строку найдет поиск на 07/01/2015?
по состоянию на 07.01.2015 не найдет ничего
и это правильно, т.к. на указанную дату запись не существовала
это именно та особенность, которую предоставляет dt_end

>А индекс только на dt_begin будет менее эффективен?
конечно
он менее селективен
в вашем примере при предикате dt_begin <= 16.01.2015 по индексу будет выбрано 3 записи,
а при предикате dt_begin <= 16.01.2015 and dt_end > 16.01.2015 - всего одна

>Кщд © (19.02.16 07:56) [53]
>по состоянию на 07.01.2015 не найдет ничего
>и это правильно, т.к. на указанную дату запись не существовала
>это именно та особенность, которую предоставляет dt_end

Объясните что это правильно тому пользователю, которому нужно здесь и сейчас занести данные, а ему из справочника вываливается "неизвестно". Я думаю он по достоинству оценит эту замечательную особенность.
Мало того. Есть логика процесса и логика реализации. Так вот по логике процесса если вы убираете промежуточную строку, действие предыдущей должно автоматически распространяться до следующей нормальной. А логика реализации должна, по идее воплощать логику процесса, а не ломать ее.

>он менее селективен
Конечно менее. Но насколько это критично по времени? При разнице на 6-7 порядков, может быть разница и будет заметна. При 1-3 порядках даже зафиксировать не удастся.

>Кщд © (19.02.16 07:56) [53]

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

>Сергей Суровцев © (19.02.16 09:17) [55]
>То есть мы говорим о двух принципиально разных подходах.
ваш подход - частный случай SCD type 2
1. он менее гибок в смысле бизнес-логики
2. неэффективен с точки зрения БД
лично я не вижу смысла решать частную задачу, когда за то же время легко и свободно пишется универсальное решение
свою точку зрения никому не навязываю
дискуссию со своей стороны заканчиваю)

Для начала еще надо решить - а допустимо ли вообще удалять записи об истории? Ведь при этом история в БД искажается и перестает соответствовать реальной истории.

Если недопустимо, то я бы выбрал более селективный вариант (с DATE_END). А если допустимо, то все равно выбрал бы его, потому что при нем пропуски в истории хотя бы можно обнаружить.

Но это вообще, для случая сферического коня в вакууме. А конкретно - все зависит от реальной задачи.

Вариант А

1) 01/01/2015 --- 05/01/2015 Гнилушин переулок
2) 05/01/2015 --- 05/01/2015 Юбилейная улица
3) 05/01/2015 --- 25/01/2015 Юбилейная Улица
4) 25/01/2015 --- 05/01/2999 Неширокая улица между магазином №2 и столовой №11

Дубль строки 2 и 3. Удаляя строку 3 (законно, как повторную) имеем разрыв с 5 по 25е.

Вариант Б

1) 01/01/2015 --- 05/01/2015 Гнилушин переулок
2) 05/01/2015 --- 25/01/2115 Юбилейная улица

3) 25/01/2115 --- 30/01/2015 Неширокая улица между магазином №2 и столовой №11 (до исправления)
3) 25/01/2015 --- 30/01/2015 Неширокая улица между магазином №2 и столовой №11 (после исправления)

4) 30/01/2015 --- 25/01/2999 Лукоморье улица

После исправления опечатки строка 2 своей датой окончания перекрывает ближайшие 100 лет.

И таких вариантов будет масса. Перед КАЖДЫМ использованием справочника придется проверять его на адекватность. Или после каждого редактирования. И обвешивать каждое изменение кучей проверок.

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

> Сергей Суровцев © (19.02.16 19:28) [58]

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

> [58] Сергей Суровцев © (19.02.16 19:28)
> 1) 01/01/2015 --- 05/01/2015 Гнилушин переулок
> 2) 05/01/2015 --- 25/01/2115 Юбилейная улица

В один день два названия. Почему не:
1) 01/01/2015 --- 05/01/2015 Гнилушин переулок
2) 06/01/2015 --- 25/01/2115 Юбилейная улица
?

>Юрий Зотов © (19.02.16 21:08) [59]
>Грязная база - это не аргумент. Грязи просто не должно быть. И для этого существуют внешние ключи, индексы, триггеры и т.п.

Согласен, грязи не должно быть. Что и обеспечится кучей обвески. Не забываем еще править DT_END предыдущей по хронологии записи после каждого добавления-редактирования-удаления.
А за появление грязи, когда она все же возникнет, ибо фантазия юзера безгранична, расстрел этого юзера перед всем коллективом и экзекуция остальным в виде недельного чтения вслух SCD type 2, без сохранения оплаты за этот период.

>Inovet © (19.02.16 21:28) [60]
>В один день два названия. Почему не:

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

Кстати, равно как и исключить перекрытие интервалов, при которых актуальными могут считаться несколько значений.

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