Каковы некоторые из лучших практик для отслеживания обновлений записи с течением времени в SQL?

Question

У меня есть таблица продуктов с первичным ключом @productid (bigint), номером продукта (int) и версией (int)

Каждый раз, когда кто-то вносит изменения только в запись о продукте, я планирую вставить в базу данных новую строку с тем же номером продукта и номером версии + 1. Это даст мне историческое отслеживание, необходимое для записи, потому что я могу видеть версия меняется с течением времени.

/* Selecting the current version is simple */
Select top 1 *
from products
where productnumber = @productnumber
order by version desc  

Однако моя проблема связана с таблицами отношений "один-ко-многим" или "многие-ко-многим" внешнего ключа. Эта таблица указывает на многие другие (например, цены на продукты с диапазонами дат, категориями продуктов и т. Д.), Которые также необходимо отслеживать.

/* Product categories, pricing */ 
 /* Should I use @productnumber here? How do I track changes to these records? */

select name
from productcategories
where productid = @productid

select price
from productpricing
 where productid = @productid and
       StartDate > @StartDate and
       EndDate <@Enddate

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

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

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

Есть ли лучшие методы или практики? Это будет кошмар производительности?

Answer 1

Если вы используете более новую версию SQL Server, вам следует изучить временные таблицы, так как это может быть вашим лучшим вариантом.

Если вам нужно поддерживать более старые версии, мой предпочтительный метод состоит в том, чтобы иметь таблицу истории с новым столбцом PK, флагом изменения (I,U,D), измененной датой, пользователем, который внес изменение, и всеми столбцами из основной стол. Затем я индексирую столбец, связанный с PK таблицы не-истории. Триггеры не слишком сильно влияют на производительность, если вы не вкладываете в них логику. Пример (псевдокод):

Table: Car
Column: CarID INT IDENTITY(1,1) Primary Key
Column: Name varchar

Table: Car_hist
Column: Car_histID INT IDENTITY(1,1) Primary Key
Column: Change char(1)
Column: DateOfChange DateTime2
Column: ChangedByUser (varchar or int)
Column: CarID <-add a unique non-clustered index
Column: Name varchar

Вы можете написать генератор на SQL, который генерирует скрипт для создания таблицы истории, индексов и т. Д. Это помогает, если у вас есть согласованная практика проектирования таблиц.

Теперь причина: мне редко приходится запрашивать таблицы истории, но когда я это делаю, почти всегда одна запись видит, что произошло и кто ее изменил. Этот метод позволяет быстро выбрать из истории значение PK родительской таблицы и легко прочитать его как журнал исторических изменений (кто что изменил и когда). Я не вижу, как вы можете сделать это в своем дизайне. Если вы действительно ловкий, вы можете найти или написать сетку, которая будет различать строки для вас, и вы сможете быстро увидеть, что изменилось.