Проектная база данных, относящаяся к атрибуту времени

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

Источник

5 ответов

Решение

Вот модель для достижения ваших заявленных требований.

Ссылка на модель данных временного ряда

Ссылка на нотацию IDEF1X для тех, кто не знаком со стандартом реляционного моделирования.

  • Нормализуется до 5NF; нет повторяющихся столбцов; Нет обновлений Аномалии, нет нулей.

  • Когда статус продукта изменяется, просто вставьте строку в ProductStatus с текущим значением даты / времени. Не нужно трогать предыдущие строки (которые были истинными и остаются верными). Никакие фиктивные значения, которые инструменты интерпретации (кроме вашего приложения) не должны интерпретировать.

  • DateTime - это фактическое DateTime, в которое Продукт был помещен в этот Статус; "От", если хотите. "До" легко выводится: это DateTime следующей (DateTime > "From") строки для Product; там, где он не существует, значением является текущий DateTime (используйте ISNULL).

Первая модель завершена; (ProductId, DateTime) достаточно для обеспечения уникальности первичного ключа. Однако, поскольку вы запрашиваете скорость для определенных условий запроса, мы можем улучшить модель на физическом уровне и предоставить:

  • Индекс (у нас уже есть индекс PK, поэтому мы сначала улучшим его, прежде чем добавить второй индекс) для поддержки покрытых запросов (запросы, основанные на любом расположении { ProductId | DateTime | Status }, могут быть предоставлены индексом без необходимости перейти к строкам данных). Что изменяет отношение Status::ProductStatus с Неидентифицирующая (пунктирная линия) на Идентифицирующий тип (сплошная линия).

  • Расположение PK выбирается на основе того, что большинство запросов будут временными рядами, основанными на Product⇢DateTime⇢Status.

  • Второй индекс предназначен для повышения скорости запросов на основе статуса.

  • В Альтернативном Соглашении это полностью изменено; т.е. мы в основном хотим текущий статус всех продуктов.

  • Во всех версиях ProductStatus столбец DateTime во вторичном индексе (не PK) имеет значение DESCending; самый последний - первый.

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

Ответы на комментарии

Сообщить о всех продуктах с текущим состоянием 2

SELECT  ProductId,
        Description
    FROM  Product       p,
          ProductStatus ps
    WHERE p.ProductId = ps.ProductId  -- Join
    AND   StatusCode  = 2             -- Request
    AND   DateTime    = (             -- Current Status on the left ...
        SELECT MAX(DateTime)          -- Current Status row for outer Product
            FROM  ProductStatus ps_inner
            WHERE p.ProductId = ps_inner.ProductId
            )

  • ProductId Индексируется, ведущий col, обе стороны

  • DateTime в индексированном, 2-й столбец в закрытом варианте запроса

  • StatusCode индексируется, 3-й столбец в опции покрытого запроса

  • поскольку StatusCode в индексе есть DESCending, для выполнения внутреннего запроса требуется только одна выборка

  • строки требуются одновременно для одного запроса; они близки друг к другу (благодаря Clstered Index); почти всегда на одной странице из-за короткого размера строки.

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

Две даты в ProductStatus

Столбцы типа DateTimeFrom и DateTimeTo являются грубыми ошибками. Давайте рассмотрим это в порядке важности.

  1. Это грубая ошибка нормализации. DateTimeTo легко выводится из единственного DateTime следующей строки; поэтому он является избыточным, дубликат столбца.

    • Точность не входит в это: это легко решается с помощью DataType (DATE, DATETIME, SMALLDATETIME). Независимо от того, отображаете ли вы одну секунду, микросекунду или наносекунду, это бизнес-решение; это не имеет ничего общего с данными, которые хранятся.

  2. Реализация столбца DateTo является 100% дубликатом (DateTime следующей строки). Это занимает вдвое больше места на диске. Для большого стола это было бы значительным ненужным мусором.

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

  4. И вдвое больше пространства кеша (или, иначе говоря, только половина строк поместится в любое пространство кеша).

  5. Вводя дубликат столбца, вы ввели возможность ошибки (теперь значение можно получить двумя способами: из дубликата столбца DateTimeTo или DateTimeFrom следующей строки).

  6. Это также аномалия обновления. Когда вы обновляете любой DateTimeFrom, обновляется, DateTimeTo предыдущей строки должен быть извлечен (нет ничего сложного, поскольку он близок) и Обновлен (большой разницы, поскольку это дополнительный глагол, которого можно избежать).

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

Обратите внимание, что если правило самого высокого порядка (нормализация) сохраняется, весь набор проблем более низкого порядка устраняется.

Думайте в терминах наборов

  • Любой, кто испытывает "трудности" или испытывает "боль" при написании простого SQL-кода, страдает при выполнении своих рабочих функций. Обычно разработчик не думает с точки зрения наборов, а реляционная база данных является моделью, ориентированной на наборы.

  • Для запроса выше нам нужен Current DateTime; Поскольку ProductStatus представляет собой набор состояний продукта в хронологическом порядке, нам просто необходим последний или MAX(DateTime) набора, принадлежащего продукту.

  • Теперь давайте посмотрим на что-то якобы "сложное", с точки зрения наборов. Для отчета о продолжительности пребывания каждого Продукта в определенном состоянии: DateTimeFrom - это доступный столбец, который определяет горизонтальное ограничение, поднабор (мы можем исключить более ранние строки); DateTimeTo является самым ранним из подмножества состояний продукта.

SELECT               ProductId,
                     Description,
        [DateFrom] = DateTime,
        [DateTo]   = (
        SELECT MIN(DateTime)                        -- earliest in subset
            FROM  ProductStatus ps_inner
            WHERE p.ProductId = ps_inner.ProductId  -- our Product
            AND   ps_inner.DateTime > ps.DateTime   -- defines subset, cutoff
            )
    FROM  Product       p,
          ProductStatus ps
    WHERE p.ProductId = ps.ProductId 
    AND   StatusCode  = 2             -- Request

  • Мышление с точки зрения получения следующей строки ориентировано на строку, а не на обработку множества. Ухудшается при работе с базой данных, ориентированной на множество. Пусть Оптимизатор сделает все это за вас. Проверьте свой ШОУ-ПЛАН, это прекрасно оптимизирует.

  • Неспособность мыслить в наборах, ограниченная тем самым написанием только одноуровневых запросов, не является разумным оправданием для: реализации масштабного дублирования и обновления аномалий в базе данных; тратить онлайн ресурсы и дисковое пространство; гарантируя половину производительности. Гораздо дешевле научиться писать простые подзапросы SQL для получения легко получаемых данных.

Источник

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

Что ж, существует книга на 400 страниц под названием "Временные данные и реляционная модель", которая решает вашу проблему.

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

Во введении книги также прямо говорится, что "эта книга не о технологии, которая (коммерчески) доступна любому пользователю сегодня".

Все, что я могу заметить, это то, что пользователи, которым нужны временные функции от систем SQL, проще говоря, оставляют желать лучшего.

PS

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

Источник

Таблицы, подобные этим:

product
-----------
product_id
status_id
name

status
-----------
status_id
name

product_history
---------------
product_id
status_id
status_time

затем написать триггер на продукт, чтобы записать статус и метку времени (sysdate) для каждого обновления, где статус меняется

Источник

альтернативный текст

Источник

Google "двухвременные базы данных" и "медленно меняющиеся измерения".

Это два названия по сути одного и того же шаблона.

Вам необходимо добавить два столбца отметок времени в таблицу продуктов "VALID_FROM" и "VALID_TO".

Когда статус вашего продукта изменяется, вы добавляете новую строку с "VALID_FROM" of now() для некоторых других известных действующих данных / времени и устанавливаете для "VALID_TO" значение 9999-12-31 23:59:59 или какую-то другую дату, смешно далеко в будущее. Вам также необходимо записать дату "9999-12-31..." в предыдущей текущей строке в текущее время "VALID_FROM" - 1 микросекунда.

Затем вы можете легко запросить статус продукта в любой момент времени.

Источник
Другие вопросы по тегам