Структура базы данных для хранения исторических данных

Наша система расчета заработной платы использует даты вступления в силу во многих таблицах. Таблица 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

Это хорошее начало.

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

Попробуйте такую ​​схему:

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.

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

Если вы искренне интересуетесь такими проблемами, я могу только предложить вам серьезно взглянуть на «Временные данные и реляционная модель».

Предупреждение1: здесь нет SQL, и почти все, что вы думаете о реляционной модели, будет заявлено как ложь. По уважительной причине.

Предупреждение2: от вас ожидается, что вы будете думать и много думать.

Предупреждение3: в книге говорится о том, как должно выглядеть решение для этого конкретного семейства проблем, но, как сказано во введении, она не о какой-либо технологии, доступной сегодня.

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

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

В вашей таблице заказов не должно быть сотен столбцов. У вас будет таблица заказов и подробная таблица заказов из-за отношений "один-ко-многим". Таблица заказов будет включать номер заказа. идентификатор клиента 9, чтобы вы могли искать все, что этот клиент когда-либо заказывал, даже если имя изменилось), имя клиента, адрес клиента (обратите внимание, что вам не нужен почтовый индекс города и т. д., введите адрес в одно поле), дату заказа и, возможно, несколько других полей, которые относятся непосредственно к заказу на верхнем уровне. Затем у вас есть таблица деталей заказа, в которой есть номер заказа, detail_id, номер детали, описание детали (это может быть объединение нескольких полей, таких как размер, цвет и т. Д., Или вы можете выделить наиболее распространенные), количество элементов, тип единицы, цена за единицу, налоги, общая цена, дата отгрузки, статус. Вы вводите одну запись для каждого заказанного товара.

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

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