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