Временная структура базы данных с твист (живые против строк строк)

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

Я буду разбивать его на сообщения с тегами ради примера, но мой прецедент немного более общий (с участием медленно меняющихся измерений - http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Предположим, что у вас есть таблица сообщений, таблица тегов и таблица post2tag:

posts (
 id
)

tags (
 id
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id)
)

Мне нужно несколько вещей:

Возможность показать, как выглядит сообщение в произвольном datetime, в том числе для удаленных строк.
Следите за тем, кто редактирует, для полного аудита.
Требуется набор материализованных представлений ( "живых" таблиц) для сохранения ссылочной целостности (т.е. регистрация должна быть прозрачной для разработчиков).
Нужно быть соответствующим образом быстро для живых и последних черновиков строк.
Возможность иметь черновик сообщения сосуществует с живым сообщением.

Я изучал различные варианты. Пока что лучшее, что я придумал (без очков # 4/# 5), немного похоже на SCD type6-hybrid setup, но вместо того, чтобы иметь текущее логическое, есть материализованное представление для текущей строки. Для всех целей и целей это выглядит так:

posts (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

post_revs (
 id,
 rev pkey,
 public,
 created_at,
 created_by,
 deleted_at
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)


tag_revs (
 id,
 public,
 rev pkey,
 created_at,
 created_by,
 deleted_at
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by
)

post2tag_revs (
 post_id,
 tag_id,
 post_rev fkey post_revs(rev), -- the rev when the relation started
 tag_rev fkey tag_revs(rev), -- the rev when the relation started
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_rev, tag_rev)
)

Я использую pg_temporal для поддержания индексов в период (created_at, deleted_at). И я держу различные таблицы в синхронизации с помощью триггеров. Yada yada yada... Я создал триггеры, которые позволяют отменить редактирование сообщений/тегов таким образом, что черновик будет храниться в оборотах без публикации. Он отлично работает.

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

Я рассмотрел возможность введения дублирования данных (т.е. n строк post2tag, введенных для каждого проекта пересмотра). Такие работы, но, как правило, намного медленнее, чем хотелось бы.

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

Я рассмотрел всевозможные флаги...

Итак, вопрос: существует ли общепринятое средство управления живыми и неживыми строками в контролируемой среде с версией строки? А если нет, то что вы пробовали и были достаточно успешны?

Ответы

Ответ 1

Думаю, я прибил его. В основном вы добавляете (уникальное) поле проекта в соответствующие таблицы, и вы работаете над черновиками, как если бы они были новым сообщением/тегом/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

post_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)


tag_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by,
 pkey (post_id, tag_id)
)

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)

Ответ 2

Якорное моделирование - отличный способ реализовать временный дБ - см. Статья в Википедии. Занимает некоторое время, чтобы привыкнуть, но работать хорошо. Существует инструмент

[Generate --> SQL Code] создаст DDL для таблиц, представлений и функций "точка-точка". Код довольно длинный, поэтому я не размещаю его здесь. Проверьте код - вам, возможно, потребуется его изменить для вашей БД.

Вот файл для загрузки в инструмент моделирования.

<schema>
<knot mnemonic="EXP" descriptor="Expired" identity="smallint" dataRange="char(1)">
<identity generator="true"/>
<layout x="713.96" y="511.22" fixed="true"/>
</knot>
<anchor mnemonic="US" descriptor="User" identity="int">
<identity generator="true"/>
<attribute mnemonic="USN" descriptor="UserName" dataRange="varchar(32)">
<layout x="923.38" y="206.54" fixed="true"/>
</attribute>
<layout x="891.00" y="242.00" fixed="true"/>
</anchor>
<anchor mnemonic="PO" descriptor="Post" identity="int">
<identity generator="true"/>
<attribute mnemonic="TIT" descriptor="Title" dataRange="varchar(2)">
<layout x="828.00" y="562.00" fixed="true"/>
</attribute>
<layout x="855.00" y="471.00" fixed="true"/>
</anchor>
<anchor mnemonic="TG" descriptor="Tag" identity="int">
<identity generator="true"/>
<attribute mnemonic="TGT" descriptor="TagText" dataRange="varchar(32)">
<layout x="551.26" y="331.69" fixed="true"/>
</attribute>
<layout x="637.29" y="263.43" fixed="true"/>
</anchor>
<anchor mnemonic="BO" descriptor="Body" identity="int">
<identity generator="true"/>
<attribute mnemonic="BOT" descriptor="BodyText" dataRange="varchar(max)">
<layout x="1161.00" y="491.00" fixed="true"/>
</attribute>
<layout x="1052.00" y="465.00" fixed="true"/>
</anchor>
<tie timeRange="datetime">
<anchorRole role="IsTagged" type="PO" identifier="true"/>
<anchorRole role="IsAttached" type="TG" identifier="true"/>
<anchorRole role="BYAuthor" type="US" identifier="false"/>
<knotRole role="Until" type="EXP" identifier="false"/>
<layout x="722.00" y="397.00" fixed="true"/>
</tie>
<tie timeRange="datetime">
<anchorRole role="Contains" type="PO" identifier="true"/>
<anchorRole role="ContainedIn" type="BO" identifier="false"/>
<layout x="975.00" y="576.00" fixed="true"/>
</tie>
<tie>
<anchorRole role="CreatedBy" type="TG" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="755.10" y="195.17" fixed="true"/>
</tie>
<tie>
<anchorRole role="CreatedBy" type="PO" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="890.69" y="369.09" fixed="true"/>
</tie>
<tie>
<anchorRole role="ModifiedBy" type="BO" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="1061.81" y="322.34" fixed="true"/>
</tie>
</schema>

Ответ 3

Я реализовал временную базу данных с использованием правил SCD типа 2 и правил и триггеров PostgreSQL и завернул их в автономный пакет для ActiveRecord: http://github.com/ifad/chronomodel

Дизайн не зависит от языка/структуры, но вы можете создавать правила и триггеры вручную, а база данных будет заботиться обо всем остальном. Посмотрите https://github.com/ifad/chronomodel/blob/master/README.sql.

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

Ответ 4

post2tag_revs имеет проблему в том, что он пытается выразить 2 принципиально разных понятия.

Тэг, примененный к проекту post post, всегда применяется только к одной ревизии, если только ревизия не публикуется.

Как только тег публикуется (т.е. связан с опубликованной пост-ревизией), он применяется к каждой будущей ревизии сообщения до ее отмены.

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

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

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

Ответ 5

Используйте только 3 таблицы: сообщения, теги и post2tag.

Добавить столбцы start_time и end_time ко всем таблицам. Добавьте уникальный индекс для ключа, start_time и end_time. Добавьте уникальный индекс для ключа, где end_time имеет значение NULL. Добавить триггеры.

Для тока:

SELECT ... WHERE end_time IS NULL

Время:

WHERE (SELECT CASE WHEN end_time IS NULL
THEN (start_time <= at_time)
ELSE (start_time <= at_time AND end_time > at_time)
END)

Поиск текущих данных не медленный из-за функционального индекса.

Edit:

CREATE UNIQUE INDEX ... ON post2tag (post_id, tag_id) WHERE end_time IS NULL;
CREATE UNIQUE INDEX ... ON post2tag (post_id, tag_id, start_time, end_time);

FOREIGN KEY (post_id, start_time, end_time) REFERENCES posts (post_id, start_time, end_time) ON DELETE CASCADE ON UPDATE CASCADE;
FOREIGN KEY (tag_id, start_time, end_time) REFERENCES tags (tag_id, start_time, end_time) ON DELETE CASCADE ON UPDATE CASCADE;