Дизайн таблицы истории SQL

Ответ 1

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

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

Если вы имеете дело с столбцами отдельно, то есть вы записываете только одно значение столбца для каждой записи истории, вам придется разработать полиморфный тип данных, который способен точно представлять каждое значение столбца, с которым вы столкнетесь.

Если эффективность пространства для хранения является вашей главной задачей, я бы разбил историю на несколько таблиц. Это означало бы наличие новой таблицы значений столбцов, связанной как с таблицей событий редактирования, так и с таблицей определения столбцов. В таблице событий редактирования будет записана метка пользователя и времени, таблица определения столбца будет записывать таблицу, столбец и тип данных. Как отметил @njk, вам не нужно значение старого столбца, потому что вы всегда можете запросить предыдущее редактирование, чтобы получить старое значение. Основная причина, по которой этот подход должен был сэкономить, - это предположение, что, вообще говоря, пользователи будут редактировать небольшое подмножество доступных полей.

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

Ответ 2

Я бы рекомендовал, чтобы для каждой таблицы, которую вы хотите отслеживать историю, у вас есть вторая таблица (т.е. tblCustomer и tblCustomer_History) с одинаковым форматом - плюс столбец даты.

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

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

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

Ответ 3

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

пользователь (id, user_id, datetime, author,...)

Примеры данных

id  user_id  datetime          author  user_title  user_name  user_tele ...
1   1        2012-11-05 11:05  Bob
2   1        2012-11-07 14:54  Tim
3   1        2012-11-12 10:18  Bob