Мнения о дизайне базы данных датчиков/чтения/предупреждения

В последнее время я задал несколько вопросов о дизайне базы данных, возможно, слишком много;-) Однако я верю, что я медленно вникаю в суть дела с моим дизайном и медленно его кипятил. Я все еще борюсь с несколькими решениями относительно того, как "предупреждения" хранятся в базе данных.

В этой системе предупреждение представляет собой объект, который должен быть подтвержден, действовать и т.д.

Изначально я рассказывал о таких предупреждениях (очень сокращенно): -

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

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

Я думал упростить вещи, зачем даже беспокоиться о таблице SensorAlertReading? Вместо этого я мог бы сделать это:

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

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

Теперь, глядя со стороны как разработчик/администратор базы данных, это заставит вас хотеть болеть или это кажется разумным?

Возможно, есть еще один способ сделать это, чтобы я мог отсутствовать?

EDIT: Здесь другая идея - она работает по-другому. Он сохраняет каждое изменение состояния сенсора, переходя от обычного к предупреждению в таблице, а затем показания просто связаны с определенным состоянием. Кажется, это решает все проблемы - что вы думаете? (единственное, что я не уверен в вызове таблицы "SensorState", я не могу не думать о лучшем имени (возможно, SensorReadingGroup?): -

Ответы

Ответ 1

Отредактировано 01 янв 11 21:50 UTC

Модель данных

Я думаю, что ваша модель данных должна выглядеть так: ▶ Модель данных датчиков ◀. (Страница 2 относится к вашей другой истории вопроса).

Читатели, которые не знакомы с стандартом реляционного моделирования, могут найти ▶ IDEF1X Notation ◀.

Бизнес (правила, разработанные в комментариях)

Я идентифицировал некоторые ранние бизнес-правила, которые теперь устарели, поэтому я удалил их

Они могут быть "прочитаны" в отношениях (читаются рядом с моделью данных). Бизнес-правила и все подразумеваемые ссылочные данные и целостность данных могут быть реализованы и, следовательно, гарантированы правилами RULES, CHECK Constraints в любой базе данных ISO SQL. Это демонстрация IDEF1X в разработке как реляционных ключей, так и сущностей и отношений. Обратите внимание, что фразы глаголов более чем просто процветают.

Помимо трех справочных таблиц, единственными статическими идентифицирующими объектами являются Location, NetworkSlave и User. Датчик является центральным для системы, поэтому я получил его собственный заголовок.

Местоположение

A Location содержит один-ко-многим Sensors
A Location может иметь один Logger

NetworkSlave

NetworkSlave собирает показания для сетевых сенсоров "один ко многим"

Пользователь

An User может поддерживать нуль-ко-многим Locations
An User может поддерживать нуль-ко-многим Sensors
An User может поддерживать нуль-ко-многим NetworkSlaves
User может выполнять нуль-ко-многим Downloads
An User может делать ноль-ко-многим Acknowledgements, каждый на одном Alert
An User может принимать значение "0 ко многим" Actions, каждый из которых ActionType

датчик

A SensorType устанавливается как ноль-ко-многим Sensors
A Logger (дома и) собирает Readings для одного LoggerSensor
A Sensor является либо одним NetworkSensor, либо одним LoggerSensor
- A NetworkSensor записи Readings, собранные одним NetworkSlave
  ,
A Logger периодически Downloaded один раз во много раз
- A LoggerSensor записи Readings, собранные одним Logger
  ,
A Reading можно считать в Alert одного AlertType
- Возможно, что AlertType может выполняться на ноль-ко-многим Readings
  ,
An Alert может быть одним Acknowledgement, одним пользователем .
An Acknowledgement может быть закрыто одним Action, одним ActionType, одним User
- An ActionType может быть применен к ноль-ко-многим Actions

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

Стоящие столбцы Id во всем, что движется, мешают определению идентификаторов, естественным реляционным ключам, которые дают вашей базе данных "силу". Это суррогатные ключи, что означает дополнительный ключ и индекс, и это мешает этой реляционной силе; что приводит к большему количеству объединений, чем в противном случае. Поэтому я использую их только тогда, когда реляционный ключ становится слишком громоздким, чтобы перейти к дочерним таблицам (и принять наложенное дополнительное соединение).
Отказоустойчивые ключи - классический симптом Ненормализованной базы данных. Нуль в базе данных - плохая новость для производительности; но Nulls in FKs означает, что каждая таблица делает слишком много вещей, имеет слишком много значений, а результаты - очень плохой код. Хорошо для людей, которые любят "реорганизовывать" свои базы данных; совершенно ненужно для реляционной базы данных.
Разрешено: Alert может быть Acknowledged; Acknowledgement может быть Actioned.
Столбцы над строкой являются Первичным ключом (см. Notation document). SensorNo - порядковый номер внутри LocationId; обратитесь к бизнес-правилам, это не имеет смысла за пределами Location; два столбца вместе образуют PK. Когда вы готовы ВСТАВИТЬ датчик (после того, как вы проверили, что попытка действительна и т.д.), Он получается следующим образом. Это исключает LoggerSensors, которые равны нулю:
```
INSERT Sensor VALUES (
    @LocationId,
    SensorNo = ( SELECT ISNULL(MAX(SensorNo), 0) + 1
        FROM Sensor
        WHERE LocationId = @LocationId
        )
    @SensorCode
    )
```
Для точности или улучшения значения я изменил NetworkSlave monitors NetworkSensor на NetworkSlave collects Readings from NetworkSensor.
Проверить ограничения. NetworkSensor и LoggerSensor являются эксклюзивными подтипами Sensor, и их целостность может быть задана ограничениями CHECK. Alerts, Acknowledgements и Actions не являются подтипами, но их целостность устанавливается одним и тем же методом, поэтому я перечислил их вместе.
- Каждое отношение в модели данных реализуется как CONSTRAINT в дочернем (или подтипе) как FOREIGN KEY (child_FK_columns). ССЫЛКИ Родительский (PK_columns)
- Для определения подтипа a Sensor требуется дискриминатор. Это SensorNo = 0 для LoggerSensors; и ненулевое значение для NetworkSensors.
- Существование NetworkSensors и LoggerSensors ограничено FK CONSTRAINTS до NetworkSlave и Logger соответственно; а также к датчику.
- В NetworkSensor включить ограничение CHECK, чтобы гарантировать, что SensorNo отличен от нуля
- В LoggerSensor включить ограничение CHECK, чтобы гарантировать, что SensorNo равен нулю
- Существование Acknowledgements и Actions ограничено идентифицированными FK CONSTRAINTS (An Acknowledgement не может существовать без Alert, а Action не может существовать без Acknowledgement). И наоборот, a Alert без Acknowledgement находится в непризнанном состоянии; a Alert с и Acknowledgement, но no Action находится в подтвержденном, но неактивном состоянии. ,
Оповещения. Концепция в дизайне такого приложения (живой мониторинг и предупреждение) - это множество небольших программ, работающих независимо друг от друга; все используют базу данных как единственную версию правды. Некоторые программы вставляют строки (Readings, Alerts); другие программы проводят опрос db на наличие таких строк (и отправляют SMS-сообщения и т.д., или ручные устройства выбирают предупреждения, относящиеся только к устройству). В этом смысле db является a, который можно охарактеризовать как поле сообщения (одна программа помещает строки в которые другая программа читает и действия).

Предполагается, что Readings для Sensors записывается "вживую" с помощью NetworkSlave, и каждую минуту или около того вставляется новый набор Readings. Фоновый процесс выполняется периодически (каждую минуту или что-то еще), это основная "мониторная" программа, в ней будет много функций. Одной из таких функций будет мониторинг Readings и создание Alerts, которые произошли со времени последней итерации (цикла программы).

Внутри цикла будет выполняться следующий сегмент кода: по одному для каждого AlertType. Это классическая проекция:
```
-- Assume @LoopDateTime contains the DateTime of the last iteration
INSERT Alert
    SELECT LocationId,
           SensorNo,
           ReadingDtm,
           "L"          -- AlertType "Low"
        FROM Sensor  s,
             Reading r
        WHERE s.LocationId = r.LocationId
        AND   s.SensorNo   = r.SensorNo
        AND   r.ReadingDtm > @LoopDtm
        AND   r.Value      < s.LowerLimit
INSERT Alert
    SELECT LocationId,
           SensorNo,
           ReadingDtm,
           "H"          -- AlertType "High"
        FROM Sensor  s,
             Reading r
        WHERE s.LocationId = r.LocationId
        AND   s.SensorNo   = r.SensorNo
        AND   r.ReadingDtm > @LoopDtm
        AND   r.Value      > s.UpperLimit
```
Таким образом, Alert определенно является фактом, который существует как строка в базе данных. Впоследствии это может быть Acknowledged с помощью User (другая строка/факт) и Actioned с ActionType на User.
Другое, что это (создание действием Projection), т.е. общий и неизменный случай, я бы назвал Alert только как строку в Alert; статический объект после создания.
Проблемы, связанные с изменением Users. Об этом заботятся уже, как следует. В верхней части моего (пересмотренного вчера) ответа я утверждаю, что основные идентификационные элементы статические. Я изменил правила бизнес-правил, чтобы улучшить ясность.
- По причинам, о которых вы говорите, User.Name не является хорошим PK для User, хотя он остается альтернативным ключом (уникальным) и тем, который используется для взаимодействия с человеком.
- User.Name не может быть дублировано, не может быть более одного Fred; может быть в терминах FirstName-LastName; два Fred Bloggs, но не в терминах User.Name. Наш второй Фред должен выбрать другой User.Name. Обратите внимание на идентифицированные индексы.
- UserId - постоянная запись, и это уже ПК. Никогда не удаляйте User, это имеет историческое значение. На самом деле ограничения FK остановят вас (никогда не используйте CASCADE в реальной базе данных, это чистое безумие). Нет необходимости в кодах или триггерах и т.д.
- Альтернативно (для удаления Users, который никогда ничего не делал и, таким образом, освободил User.Name для использования), разрешите Delete, пока не будут обнаружены FK (т.е. UserId не ссылается на Download, Acknowledgement, Action).
Чтобы гарантировать, что только Users, которые являются текущими, выполняют Actions, добавьте IsObsolete boolean в User (Обновлено DM) и проверьте этот столбец, если эта таблица опрошена для любой функции (кроме отчетов). Вы можете реализовать a View UserCurrent, который возвращает только те Users.

То же самое относится к Location и NetworkSlave. Если вам нужно различать текущий и исторический, дайте мне знать, я добавлю к ним IsObsolete.

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

Не стесняйтесь задавать вопросы.

Обратите внимание, что документ IDEF1 Notation был расширен.

Ответ 2

Вот мои два цента по проблеме.

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

Таблица

AlertSetup позволяет настроить пороговые значения предупреждения с датчика для определенного типа предупреждения. Например, TresholdLevel= 100 и TresholdType= 'HI' должны вызывать предупреждение для показаний более 100.

Чтение таблица содержит показания датчиков, когда они передаются на сервер (приложение).

В таблице

Alert содержатся все предупреждения. Он сохраняет ссылки на первое чтение, которое вызвало предупреждение и последнее, которое завершило его (FirstReadingId, LastReadingId). IsActive истинно, если есть активное предупреждение для комбинации (SensorId, AlertTypeId). IsActive может быть установлено в значение false только при чтении ниже порога предупреждения. IsAcknowledged означает, что оператор подтвердил предупреждение.

Уровень приложения вставляет новое чтение в таблицу Чтение, захватывает ReadingId.
Затем приложение проверяет показания с настройками предупреждений для каждой комбинации (SensorId, AlertTypeId). На этом этапе создается набор объектов {SensorId, AlertTypeId, ReadingId, IsAlert} и для каждого объекта устанавливается флаг IsAlert.
Затем таблица Предупреждение проверяется на наличие активных предупреждений для каждого объекта {SensorId, AlertTypeId, ReadingId, IsAlert} из коллекции.
- Если IsAlert имеет значение ИСТИНА и нет активных предупреждений для комбинации (SensorId, AlertTypeId), новая строка добавляется в таблицу Предупреждение с FirstReadingId, указывая на текущий ReadingId. Для параметра IsActive установлено значение TRUE, а IsAcknowledged - FALSE.
- Если значение IsAlert равно TRUE, а для комбинации (SensorId, AlertTypeId) имеется активное предупреждение, эта строка обновляется установкой LastReadingId, указывающей на текущий ReadingId.
- Если IsAlert является FALSE, и для комбинации (SensorId, AlertTypeId) есть активное предупреждение, эта строка обновляется установкой IsActive FALSE.
- Если IsAlert ЛОЖЬ, и нет активных предупреждений для комбинации (SensorId, AlertTypeId), таблица Предупреждение не изменяется.

Ответ 3

Основным "треугольником", с которым вам приходится иметь дело, является Sensor, [Sensor] Reading и Alert. Предполагая, что вам нужно отслеживать активность по мере ее возникновения (в отличие от дизайна "загрузить все сразу" ), ваше третье решение похоже на то, что мы делали недавно. Несколько настроек, и это будет выглядеть так:

[Location] 
LocationId 

[Sensor] 
SensorId 
LocationId 
CurrentSensorState  --  Denormalized data!

[SensorReading] 
SensorReadingId 
SensorState
Value 
Timestamp 

[SensorStateLog] 
SensorId 
Timestamp 
SensorState
Status   --  Does what?
IsInAlert 
(Primary key is {SensorId, Timestamp})

"SensorState" может быть SensorStateId, с соответствующей таблицей списка, содержащей список (и ограничение) всех возможных состояний.

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

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

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