База данных "supertable" против большего количества таблиц против общей таблицы

Question

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

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

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

То же самое с показаниями регистратора.

Вот мои 3 идеи для хранения этих данных:

Опция 1:

Местоположение [Таблица]
- Идентификатор [ПК]
- Название
- HasLogger

LiveSensor [Таблица]
- LocationId [FK]
- Идентификатор [ПК]

LiveSensorReading [Таблица]
- Идентификатор [ПК]
- SensorId [FK]
- Значение

LiveSensorAlert [Таблица]
- Идентификатор [ПК]
- SensorReadingId [FK] (может не понадобиться - принудительное выполнение всегда должно иметь как минимум 1 чтение)

LiveSensorAlertCorrectiveAction [Таблица]
- LiveSensorAlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

LiveSensorAlertAcknowledgement [Таблица]
- LiveSensorAlertId [FK]
- ByUserID [FK]

LiveSensorAlertReading [Таблица]
- SensorAlertId [FK]
- SensorReadingId [FK]

LoggerReading [Таблица]
- LocationId [FK]
- Значение

LoggerAlert [Таблица]
- Идентификатор [ПК]
- LoggerReadingId [FK] (может не потребоваться - принудительное выполнение всегда должно иметь хотя бы 1 чтение)

LoggerAlertReading [Таблица]
- LoggerAlertId [FK]
- LoggerReadingId [FK]

LoggerAlertCorrectiveAction [Таблица]
- LoggerAlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

LoggerAlertAcknowledgement [Таблица]
 - LoggerAlertId [FK]
 - ByUserID [FK]

• Проблема: много повторяющихся таблиц (правда ли это имеет значение??)

Вариант 2:

Местоположение [Таблица]
- Я бы
- Название
- HasLogger

Датчик [Таблица]
- Идентификатор [ПК]
- LocationId [FK]

SensorReading [Таблица]
- Идентификатор [ПК]
- SensorId [FK]
- Значение

LoggerReading
- LocationId [FK]
- Значение

Оповещение [Таблица]
- Идентификатор [ПК]

AlertCorrectiveAction [Таблица]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

AlertAcknowledgement [Таблица]
- AlertId [FK]
- ByUserId [FK]

SensorAlertReading
- AlertId [FK]
- SensorReadingId [FK]

LoggerAlertReading
 - AlertId [FK]
 - LoggerReadingId [FK]

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

Вариант 3:

Местоположение [Таблица] - Id - Имя - Датчик HasLogger [Таблица] - Идентификатор [PK]
- LocationId [FK]

SensorReading [Таблица]
- Id [PK]
- SensorId [FK]
- Значение LoggerReading - LocationId [FK] - Значение Alert [ Таблица] "Супер таблица"
- Id [PK]

LoggerAlert [Таблица]
- AlertId [PK, FK]
- LoggerReadingId [FK]

SensorAlert [Таблица]
- AlertId [PK, FK]
- SensorReadingId [FK]

AlertCorrectiveAction [Таблица]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

AlertAcknowledgement [Таблица]
- AlertId [FK]
- ByUserId [FK]

SensorAlertReading [таблица]
- SensorAlertId [FK]
- SensorReadingId [FK]

LoggerAlertReading [таблица] - Fler] LoglerAlertRending [таблица] - LoggerReadingId [FK] 

• Проблема: Ничто не останавливает LoggerAlert и SensorAlert, ссылающиеся на одно и то же предупреждение (та же проблема, что и в варианте 2).
• Проблема: запутывает базу данных (разве супер таблица не является концепцией ОО? База данных должна быть чисто реляционной, не так ли?)

Я думаю, что до сих пор я предпочитаю вариант 1, потому что он кажется таким чистым и цель ясна (я надеюсь!), Хотя я эффективно повторяю таблицы.

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

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

Благодарю.

РЕДАКТИРОВАТЬ: некоторая дополнительная информация, чтобы помочь ответить на некоторые из вопросов ниже:

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

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

Регистратор имеет довольно специфические столбцы, которые находятся в таблице местоположений. Поскольку расположение и логгер были бы отображением 1: 1, я решил не иметь отдельную таблицу логгеров, и до сих пор она, кажется, работала нормально и оставалась простой. Пример столбцов: LoggerRFID (int), LoggerUpperLimit (float), LoggerLowerLimit (float) и т. Д. Вы можете почти утверждать, что регистратор является датчиком, но я пошел по этому пути, и он не получился слишком хорошо.

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

Answer 1

Некоторые мысли (идеи и мнения, а не ответы) по этому поводу:

Модель "supertable" (тип / подтип) является привлекательной, но ее сложно реализовать и поддерживать. Несколько хитростей:

ALERT
  AlertId    PK 1/2
  AlertType  PK 2/2  Check constraint (1 or 2, or better L or S)

... то есть составной первичный ключ, где "тип" всегда должен быть L) или S).

LOGALERT
  LogAlertId  PK 1/2  FK 1/2
  AlertType   PK 2/2  FK 2/2

(and again for SENSORALERT)

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

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

• Как часто вы должны иметь дело со всеми оповещениями, а не только с журналом или только с датчиком? Если в большинстве случаев вам приходится иметь дело только с одним или другим, это, вероятно, того не стоит.
• С какими деталями, относящимися к журналу или датчику, вам приходится иметь дело? Помимо реальных событий, связанных с отдельным предупреждением, насколько одинаковыми для обоих типов являются бесчисленные атрибуты (подробности в столбцах), которые вы будете отслеживать? Если пользователи, подтверждения и корректирующие действия (в достаточной степени) идентичны, вы можете сделать их атрибутами (столбцами) ALERT, но если нет, то вам придется присвоить им атрибуты соответствующего подтипа, и вы потеряете консолидирующее преимущество супертипа.
• И вы должны сделать это правильно сейчас, во время разработки. Исследуйте, задавайте вопросы, смотрите в хрустальные шары (то есть размышляйте о том, что может произойти в будущем, чтобы опровергнуть текущие предположения каждого), потому что, если вы ошибаетесь, вам и вашим преемникам, возможно, придется жить с этим вечно.

Answer 2

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

Location [Table]
- Id
- Name
- HasLogger

ObjectType [Table]
- Id [PK]
- Name -- either Sensor or Logger
- Description

Object [Table]
- Id [PK]
- LocationId [FK]
- ObjectTypeId [FK]

Reading [Table]
- Id [PK]
- ObjectId [FK]
- Value

ObjectReading
- ObjectId [FK]
- ReadingId [FK]

Alert [Table]
- Id [PK]
- ReadingId [FK]

AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]

AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]

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

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

Я также согласен с оценкой KM, приведенной выше, что идентификатор первичного ключа каждой таблицы должен называться чем-то длиннее, чем "Id".

Answer 3

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

Если вас интересует реляционная структура Supertype-Subtype, в общем, этот вопрос может вас заинтересовать.

Могу ли я предложить вам закрыть этот вопрос.