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

Question

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

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

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

posts (
 id
)

tags (
 id
)

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

Мне нужна пара вещей:

1. Возможность точно показать, как выглядело сообщение в произвольный момент времени, в том числе для удаленных строк.
2. Следите за тем, кто что редактирует, для полного аудита.
3. Необходим набор материализованных представлений ("живых" таблиц) для сохранения ссылочной целостности (т. Е. Регистрация должна быть прозрачной для разработчиков).
4. Должен быть соответственно быстрым для живых и последних черновиков строк.
5. Возможность иметь черновик поста вместе с живым постом.

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

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 для поддержания индексов по периоду (made_at, Удаленный_at). И я синхронизирую различные таблицы с помощью триггеров. Яда, яда, яда... Я создал триггеры, которые позволяют отменить редактирование сообщений / тегов таким образом, чтобы черновик сохранялся в оборотах без публикации. Работает отлично.

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

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

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

Я рассмотрел все виды флагов...

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

Answer 1

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

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

Answer 2

Якорное моделирование - хороший способ реализовать временный дБ - см. Также статью в Википедии. Требуется некоторое время, чтобы привыкнуть, но работать приятно. Существует онлайн-инструмент моделирования, и если вы загружаете предоставленный файл XML [File -> Load Model from Local File] вы должны увидеть что-то вроде этого - также используйте [Layout --> Togle Names],

[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>

Answer 3

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

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

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

Answer 4

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

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

После публикации тега (т. Е. Связанного с опубликованной ревизией поста) он применяется к каждой будущей ревизии поста, пока он не будет отозван.

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

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

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

Answer 5

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

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

Для текущего:

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)

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

Редактировать:

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;