Структура базы данных для хранения исторических данных
Предисловие. На днях я думал о новой структуре базы данных для нового приложения и понял, что нам нужен способ эффективного хранения исторических данных. Я хотел, чтобы кто-то еще посмотрел и посмотрел, есть ли проблемы с этой структурой. Я понимаю, что этот метод хранения данных вполне мог быть изобретен ранее (я почти уверен, что он есть), но я не знаю, есть ли у него имя, и некоторые поиски в Google, которые я пробовал, ничего не дали.
Проблема: Допустим, у вас есть таблица для заказов, и заказы связаны с таблицей клиентов для клиента, который разместил заказ. В обычной структуре базы данных вы можете ожидать что-то вроде этого:
orders
------
orderID
customerID
customers
---------
customerID
address
address2
city
state
zip
Довольно просто, orderID имеет внешний ключ customerID, который является первичным ключом таблицы customer. Но если мы собираемся запустить отчет по таблице заказов, мы присоединим таблицу клиентов к таблице заказов, которая вернет текущую запись для этого идентификатора клиента. Что делать, если при размещении заказа адрес клиента был другим, и он впоследствии был изменен. Теперь наш заказ больше не отражает историю адресов этих клиентов на момент размещения заказа. По сути, изменив историю клиента, мы просто изменили всю историю этого клиента.
Теперь есть несколько способов обойти это, одним из которых будет копирование записи при создании заказа. Однако я пришел к выводу, что, как мне кажется, это будет более простой способ сделать это, возможно, немного более элегантным и с дополнительным бонусом регистрации в любое время, когда вносятся изменения.
Что делать, если я вместо этого сделал такую структуру:
orders
------
orderID
customerID
customerHistoryID
customers
---------
customerID
customerHistoryID
customerHistory
--------
customerHistoryID
customerID
address
address2
city
state
zip
updatedBy
updatedOn
пожалуйста, простите форматирование, но я думаю, что вы можете увидеть идею. По сути, идея заключается в том, что каждый раз, когда клиент изменяется, вставляется или обновляется, идентификатор customerHistoryID увеличивается, а таблица клиентов обновляется с использованием новейшего идентификатора customerHistoryID. Таблица заказов теперь указывает не только на идентификатор клиента (который позволяет видеть все ревизии в записи клиента), но также на идентификатор клиента, который указывает на конкретную ревизию записи. Теперь заказ отражает состояние данных на момент создания заказа.
Добавив столбец updatedby и updatedon в таблицу customerHistory, вы также можете увидеть "журнал аудита" данных, чтобы вы могли видеть, кто и когда внес изменения.
Один потенциальный недостаток может быть удаление, но я не очень беспокоюсь об этом для этой необходимости, так как ничего не должно быть удалено. Но даже в этом случае тот же эффект может быть достигнут при использовании activeFlag или чего-то подобного в зависимости от области данных.
Я думаю, что все таблицы будут использовать эту структуру. Каждый раз, когда извлекаются исторические данные, они объединяются с таблицей истории, используя customerHistoryID, чтобы показать состояние данных для этого конкретного заказа.
Извлечь список клиентов легко, достаточно просто присоединиться к таблице клиентов по customerHistoryID.
Может кто-нибудь увидеть какие-либо проблемы с этим подходом, либо с точки зрения дизайна, либо по причинам производительности, почему это плохо. Помните, что независимо от того, что я делаю, мне нужно убедиться, что исторические данные сохраняются, чтобы последующие обновления записей не меняли историю. Есть ли способ лучше? Это известная идея, у которой есть имя, или какая-либо документация по ней?
Спасибо за любую помощь.
Обновление: это очень простой пример того, что я действительно собираюсь получить. Мое реальное приложение будет иметь "заказы" с несколькими внешними ключами к другим таблицам. Информация о месте отправления / назначения, информация о клиенте, информация об объекте, информация о пользователе и т. Д. Несколько раз мне предлагалось скопировать информацию в запись заказа в тот момент, и я видел, как это делалось много раз, но это привело бы к записи с сотнями столбцов, что на самом деле невозможно в этом случае.
7 ответов
Когда я сталкивался с такими проблемами, одна альтернатива - сделать таблицу истории. Его функции такие же, но его немного легче следовать
orders
------
orderID
customerID
address
City
state
zip
customers
---------
customerID
address
City
state
zip
РЕДАКТИРОВАТЬ: если количество столбцов становится большим по вашему вкусу, вы можете выделить его, как вам нравится.
Если вы выберете другой вариант и используете таблицы истории, вам следует рассмотреть возможность использования битемпоральных данных, поскольку вам, возможно, придется столкнуться с возможностью исправления исторических данных. Например, Клиент изменил свой текущий адрес с А на В, но вы также должны исправить адрес в существующем заказе, который в настоящее время выполняется.
Также, если вы используете MS SQL Server, вы можете рассмотреть возможность использования индексированных представлений. Это позволит вам обменять небольшое инкрементное снижение производительности вставки / обновления на большое увеличение выбора перфорации. Если вы не используете сервер MS SQL, вы можете повторить это с помощью триггеров и таблиц.
Когда вы проектируете свои структуры данных, будьте очень внимательны, чтобы хранить правильные отношения, а не то, что похоже на правильные отношения. Если адрес для заказа необходимо сохранить, то это потому, что адрес является частью заказа, а не клиентом. Кроме того, цены за единицу являются частью заказа, а не продукта и т. Д.
Попробуйте такую договоренность:
Customer
--------
CustomerId (PK)
Name
AddressId (FK)
PhoneNumber
Email
Order
-----
OrderId (PK)
CustomerId (FK)
ShippingAddressId (FK)
BillingAddressId (FK)
TotalAmount
Address
-------
AddressId (PK)
AddressLine1
AddressLine2
City
Region
Country
PostalCode
OrderLineItem
-------------
OrderId (PK) (FK)
OrderItemSequence (PK)
ProductId (FK)
UnitPrice
Quantity
Product
-------
ProductId (PK)
Price
etc.
Если вам действительно необходимо хранить историю чего-либо, например, отслеживать изменения в заказе с течением времени, то вам следует делать это с помощью таблицы журнала или аудита, а не таблиц транзакций.
Обычно заказы просто хранят информацию в том виде, в каком она была на момент заказа. Это особенно относится к таким вещам, как номера деталей, названия деталей и цены, а также адрес и имя клиента. Тогда вам не нужно объединяться в 5 или 6 таблиц, чтобы получить информацию, которая может храниться в одной. Это не денормализация, так как вам действительно нужно иметь информацию, существовавшую на момент заказа. Я думаю, менее вероятно, что наличие этой информации в таблицах заказа и сведений о заказе (хранит заказанные отдельные элементы) менее рискованно с точки зрения случайного изменения данных.
Ваша таблица заказов не будет иметь сотни столбцов. У вас будет таблица заказов и таблица деталей заказа из-за одного-многих отношений. Таблица заказов будет включать номер заказа. идентификатор клиента 9, так что вы можете искать все, что этот клиент когда-либо заказывал, даже если имя изменилось), имя клиента, адрес клиента (обратите внимание, что вам не нужен почтовый индекс штата и т. д., введите адрес в одном поле), дату заказа и, возможно, несколько других полей, которые имеют непосредственное отношение к порядку на верхнем уровне. Затем у вас есть таблица с описанием заказа, которая имеет номер заказа, detail_id, номер детали, описание детали (это может быть объединение множества полей, таких как размер, цвет и т. Д., Или вы можете выделить наиболее распространенные), Нет элементов, тип единицы, цена за единицу, налоги, общая цена, дата отгрузки, статус. Вы вводите одну запись для каждого заказанного товара.
Если вы действительно заинтересованы в таких проблемах, я могу только предложить вам серьезно взглянуть на "Временные данные и реляционная модель".
Предупреждение1: там нет SQL, и почти все, что вы знаете о реляционной модели, будет признано ложным. По уважительной причине.
Предупреждение2: вы должны думать, и думать серьезно.
Предупреждение 3: книга о том, как должно выглядеть решение этого семейства проблем, но, как говорится во введении, речь идет не о какой-либо технологии, доступной сегодня.
Тем не менее, книга является подлинным просветлением. По крайней мере, это помогает прояснить, что решение таких проблем не будет найдено в SQl в его нынешнем виде или в ORM в том виде, в каком они существуют сегодня, в этом отношении.
Наша система начисления заработной платы использует даты вступления в силу во многих таблицах. Таблица ADDRESSES основана на EMPLID и EFFDT. Это позволяет нам отслеживать каждый раз, когда адрес сотрудника меняется. Вы можете использовать ту же логику для отслеживания исторических адресов для клиентов. Ваши запросы должны просто включать пункт, который сравнивает дату заказа с датой адреса клиента, которая действовала на момент заказа. Например
select o.orderID, c.customerID, c.address, c.city, c.state, c.zip
from orders o, customers c
where c.customerID = o.customerID
and c.effdt = (
select max(c1.effdt) from customers c1
where c1.customerID = c.customerID and c1.effdt <= o.orderdt
)
Цель состоит в том, чтобы выбрать самую последнюю строку в клиентах с датой вступления в силу, которая находится на или до даты заказа. Эту же стратегию можно использовать для хранения исторической информации о ценах на продукцию.
То, что вы хотите, называется хранилищем данных. Поскольку хранилища данных - это OLAP, а не OLTP, рекомендуется иметь столько столбцов, сколько вам нужно для достижения ваших целей. В вашем случае orders Таблица в хранилище данных будет иметь 11 полей с "моментальным снимком" заказов по мере их поступления, независимо от обновлений учетных записей пользователей.
Wiley -The Data Warehouse Toolkit, Second Edition
Это хорошее начало.
Мне самому нравится быть простым. Я бы использовал две таблицы: таблицу клиентов и таблицу истории клиентов. Если у вас есть ключ (например, customerId) в таблице истории, то нет причин создавать объединяющую таблицу, выбор этого ключа выдаст вам все записи.
У вас также нет информации аудита (например, дата изменения, кто изменил и т. Д.) В таблице истории, так как вы ее показываете, я надеюсь, что вы этого хотите.
Так что мой будет выглядеть примерно так:
CustomerTable (this contains current customer information)
CustID (distinct non null)
...all customer information fields
CustomerHistoryTable
CustId (not distinct non null)
...all customer information fields
DateOfChange
WhoChanged
Поле DataOfChagne - это дата, когда таблица клиента была изменена (от значений в этой записи) до значений в более поздней записи значений в CustomerTable.
Ваша таблица заказов просто нуждается в CustomerID, если вам нужно найти информацию о клиенте во время заказа, это простой выбор.