База данных "supertable" и больше таблиц против общей таблицы
Я пытаюсь определить дизайн базы данных. В частности, это часть более крупного дизайна. В принципе, есть "местоположения" - каждое место может иметь любое количество связанных с ним датчиков, и оно может иметь регистратор (но только 1).
У меня есть показания датчика, и у меня есть показания регистратора, каждый из которых достаточно разный, чтобы оправдать отдельные таблицы.
Если показание датчика выходит за пределы диапазона, генерируется предупреждение. В то время как показания датчика остаются вне диапазона, они продолжают связываться с этим предупреждением, поэтому вы получаете 1 предупреждение, содержащее множество показаний, что позволяет мне позже отображать предупреждение, чтобы я мог определять тенденции и т.д.
То же самое с показаниями журнала.
Вот мои 3 идеи для хранения этих данных:
Вариант 1:
Location [Table]
- Id [PK]
- Name
- HasLogger
LiveSensor [Table]
- LocationId [FK]
- Id [PK]
LiveSensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value
LiveSensorAlert [Table]
- Id [PK]
- SensorReadingId [FK] (may not be needed - enforces need to always have at least 1 reading)
LiveSensorAlertCorrectiveAction [Table]
- LiveSensorAlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]
LiveSensorAlertAcknowledgement [Table]
- LiveSensorAlertId [FK]
- ByUserID [FK]
LiveSensorAlertReading [Table]
- SensorAlertId [FK]
- SensorReadingId [FK]
LoggerReading [Table]
- LocationId [FK]
- Value
LoggerAlert [Table]
- Id [PK]
- LoggerReadingId [FK] (may not be needed - enforces need to always have at least 1 reading)
LoggerAlertReading [Table]
- LoggerAlertId [FK]
- LoggerReadingId [FK]
LoggerAlertCorrectiveAction [Table]
- LoggerAlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]
LoggerAlertAcknowledgement [Table]
- LoggerAlertId [FK]
- ByUserID [FK]
- Проблема: много повторяющихся таблиц (действительно ли это имеет значение, хотя?)
Вариант 2:
Location [Table]
- Id
- Name
- HasLogger
Sensor [Table]
- Id [PK]
- LocationId [FK]
SensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value
LoggerReading
- LocationId [FK]
- Value
Alert [Table]
- Id [PK]
AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]
AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]
SensorAlertReading
- AlertId [FK]
- SensorReadingId [FK]
LoggerAlertReading
- AlertId [FK]
- LoggerReadingId [FK]
- Проблема: не применяет "по крайней мере 1
чтение на предупреждение ".
- Проблема: позволяет использовать несколько типов
чтение для ссылки на одно и то же предупреждение.
Вариант 3:
Location [Table]
- Id
- Name
- HasLogger
Sensor [Table]
- Id [PK]
- LocationId [FK]
SensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value
LoggerReading
- LocationId [FK]
- Value
Alert [Table] "super table"
- Id [PK]
LoggerAlert [Table]
- AlertId [PK, FK]
- LoggerReadingId [FK]
SensorAlert [Table]
- AlertId [PK, FK]
- SensorReadingId [FK]
AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]
AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]
SensorAlertReading [Table]
- SensorAlertId [FK]
- SensorReadingId [FK]
LoggerAlertReading [Table]
- LoggerAlertId [FK]
- LoggerReadingId [FK]
- Проблема: ничто не останавливает
LoggerAlert и SensorAlert
ссылаясь на одно и то же предупреждение (та же проблема
как вариант 2).
- Проблема: Обфускация базы данных (не
супер таблица больше концепции OO?
база данных должна быть чисто
реляционная не так ли?)
Я думаю, что до сих пор я предпочитаю вариант 1, потому что он просто кажется таким чистым, и ясное намерение (я надеюсь!), хотя я эффективно повторяю таблицы.
Единственная небольшая проблема, о которой я только подумала, заключается в том, что показания для разных датчиков все еще могут быть связаны с одним сигналом тревоги.
Мне интересно, каковы мнения людей по вышеуказанным вариантам. Я видел, что использование "супер-таблиц" часто рекомендовалось, но по какой-то причине оно просто не кажется правильным - это почти похоже на взломы, особенно когда я вижу методы для обеспечения целостности данных. Это кажется более похожим на программирование OO, чем на реляционный дизайн.
Спасибо.
EDIT:
Дополнительная информация, чтобы помочь ответить на некоторые из следующих вопросов:
В большинстве случаев база данных обрабатывается только через сервер приложений, если это имеет какое-то значение.
Сигналы оповещений и оповещений в реальном времени, как правило, обрабатываются одинаково, поэтому я, скорее всего, буду иметь дело со всеми предупреждениями большую часть времени, вместо того, чтобы иметь дело с предупреждениями о регистрации и предупреждениями по-разному.
В журнале есть довольно конкретные столбцы, которые находятся в таблице местоположений. Поскольку местоположение и регистратор будут отображать от 1 до 1, я решил не иметь отдельную таблицу журналов, и до сих пор, как представляется, он был разработан и оставался простым. Примеры столбцов: LoggerRFID (int), LoggerUpperLimit (float), LoggerLowerLimit (float) и т.д. Вы могли бы почти утверждать, что регистратор - это датчик, но я пошел по этой дороге, и это не получилось слишком хорошо.
Я могу почти согласиться с созданием предупреждений общего характера, но, как сказал один из ответов, я стараюсь быть очень уверенным в этом, поэтому продолжаю исследование до тех пор, пока я могу, прежде чем выбрать конкретный путь.
Ответы
Ответ 1
Некоторые мысли (идеи и мнения, а не ответы) об этом:
Модель "supertable" (тип/подтип) является убедительной, но может быть сложной для реализации и поддержки. Несколько трюков:
ALERT
AlertId PK 1/2
AlertType PK 2/2 Check constraint (1 or 2, or better L or S)
... т.е. составной первичный ключ, где "тип" всегда должен быть L) og или S).
LOGALERT
LogAlertId PK 1/2 FK 1/2
AlertType PK 2/2 FK 2/2
(and again for SENSORALERT)
... то есть тот же самый составной первичный ключ, а внешний ключ находится в обеих столбцах. Сделанный таким образом, может быть только одна таблица подтипов для данной таблицы типов, а верхняя таблица четко показывает, какой подтип задействован. Никакой способ обеспечить соблюдение строки в таблице подтипов, поэтому тщательно настройте свои данные. И большую часть сложности запросов можно решить (скрыть?), Используя представления.
Недостатком является то, что он сложный, запутанный тем, кто еще не знаком с ним, и потребует дополнительной поддержки и усилий. Реальный вопрос: стоит ли это?
- Как часто вы должны иметь дело со всеми предупреждениями, а не только с журналом или только с датчиком? Если в большинстве случаев вам приходится иметь дело только с одним или другим, это, вероятно, не стоит.
- С какими деталями вы будете иметь дело с конкретными данными, зависящими от Log или Sensor? Помимо фактических событий, связанных с индивидуальным предупреждением, насколько похожими по обоим типам являются несметные атрибуты (детали в столбцах), которые вы будете отслеживать? Если пользователи, знания и корректирующие действия (достаточно) идентичны, вы можете сделать их атрибутами (столбцами) ALERT, но если нет, тогда вы должны сделать их atttributes соответствующего подтипа и потеряете преимущество консолидации супертипа.
- И вы должны получить его правильно сейчас, во время разработки. Научитесь, задавайте вопросы, смотрите на хрустальные шары (то есть размышляйте, что может произойти в будущем, чтобы аннулировать все текущие предположения), потому что, если вы ошибаетесь, вам и вашим преемникам, возможно, придется жить с ним навсегда.
Ответ 2
Вы можете добавить столбец ObjectType в свои зеркальные таблицы в первом варианте и предоставить значения как Sensor, так и Logger. Тогда ваш дизайн базы данных будет выглядеть примерно так:
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]
Эта конструкция немного запутывает базовую цель базы данных, в основном потому, что я не мог придумать лучшего слова для описания "датчика или регистратора", чем "объект" - если есть какой-то конкретный термин, который мог бы все вместе описать прикрепленный в месте, что, несомненно, облегчит понимание базы данных.
Вы также можете удалить столбец Идентификатор из ObjectType и сделать имя Первичным ключом, если вы не особенно брезгливы о нецелочисленных идентификаторах в таблицах. У меня были плохие впечатления от таблиц, таких как ObjectType, где первичный ключ не является целым числом, поэтому я почти всегда использую его.
Я также согласен с оценкой KM выше, что каждый идентификатор первичного ключа таблицы должен быть назван чем-то длиннее, чем "Id".
Ответ 3
Я думаю, на этот вопрос был дан ответ в другом вопросе с полной моделью данных; в противном случае (если есть что-то непогашенное), напишите "Редактировать" на этот вопрос.
Если вы заинтересованы в реляционной структуре Supertype-Subtype, в общем смысле, этот вопрос могут вас заинтересовать.
Могу предложить вам закрыть этот вопрос.
