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

Ответ 1

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

orders
------
orderID
customerID
address
City
state
zip



customers
---------
customerID
address
City
state
zip

EDIT: если количество столбцов достигает максимума по вашему вкусу, вы можете разделить его, как вам нравится.

Если вы переходите к другому варианту и используете таблицы истории, вам следует использовать данные bitemporal, так как вам, возможно, придется иметь дело с возможность того, что исторические данные должны быть исправлены. Например, Клиент изменил свой текущий адрес от A до B, но вам также нужно исправить адрес по существующему заказу, который в настоящее время выполняется.

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

Ответ 2

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

Попробуйте следующее:

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.

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

Ответ 3

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

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

Ответ 4

Как я уже говорил ранее для аналогичного вопроса:

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

В результате я вообще отказался от идеи файла основного клиента. Вместо этого имя/информация о компании/адресе никогда не являются основными записями (за исключением таких приложений, как выставление счетов или налоги на имущество, когда конкретный физический адрес никогда не редактируется на другой адрес), это просто поля, обозначающие используемый контакт. AT A PARTICULAR POINT IN ВРЕМЯ, обычно внутри чего-то вроде записи заказа клиента. Каждый заказ клиента привязан к предыдущему и следующему порядку для этого клиента, даже когда клиент меняет свое имя или адрес. Преимущество состоит в том, что все заказы могут консолидироваться/суммироваться/анализироваться по всей истории транзакций клиента, даже если каждый заказ может отличаться от имени или адреса контакта. Это несколько противоречиво, особенно когда вы пытаетесь удовлетворить нормализацию дизайнеров db, но в итоге он становится очень гибким и удобным.

Например, когда клиент X сначала размещает заказ, запись клиента не создается. Вместо этого создается запись заказа клиента, которая содержит необходимую информацию о имени/компании/адресе, действующую на момент заказа. Когда клиент X размещает свой второй заказ, мы не ищем файл клиента, мы ищем файл заказа клиента, а затем копируем/связываем его, чтобы создать свой второй заказ. Если он хочет изменить свое имя/информацию о компании/адресе, отлично, мы просто редактируем эти поля в заказе № 2 и заказ № 1 остается неизменным. Теперь он можно найти в любом варианте (порядок 1 или 2).

Для других соображений при попытке решить, действительно ли две записи клиентов одинаковы, см. http://semaphorecorp.com/mpdd/mpdd.html

Ответ 5

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

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

Ответ 6

То, что вы хотите, называется хранилищем данных. Поскольку хранилища данных OLAP, а не OLTP, для достижения ваших целей рекомендуется иметь столько столбцов, сколько необходимо. В вашем случае таблица orders в хранилище данных будет иметь 11 полей, имеющих "моментальный снимок" заказов по мере их поступления, независимо от обновлений учетных записей пользователей.

Wiley -The Data Warehouse Toolkit, Second Edition

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

Ответ 7

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

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

Ответ 8

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

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

Warning2: вы должны думать и думать тяжело.

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

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