Проектирование БД

← →
DynaBlaster (2009-04-09 19:22) [0]

Есть два вида принципиально разных устройств.

Допустим, один тип обладает характеристиками: "a", "b" и "c".
Второй тип обладает: "d", "e" и "f".

Информацию об устройствах нужно хранить в БД, при этом у каждого устройства конкретного должен быть свой индивидуальный ID, также у устройств есть имена. Как лучше это делать, в каких таблицах?

Мои варианты:

1) в одной таблице с полями:

ID, Name, TypeDevice, a, b, c, d, e, f

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

2) в двух таблицах:

Первая: ID, Name, a, b, c
Вторая: ID, Name, d, e, f

Избыточности нету, зато более сложная работа по обеспечению уникальности ID в РАЗНЫХ таблицах. Выборка более сложная по логике: например, чтобы найти устройство по ID надо посмотреть в одну таблицу, если нету - то в другую...

3) хранить в трех таблицах:

Первая: ID, Name, TypeDevice

Вторая: REF_ID, a, b, c
Третья: REF_ID, d, e, f

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

Какой вариант выбрали бы вы и почему? Возможно, какие-то другие варианты?

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

← →
DVM © (2009-04-09 19:39) [1]

> DynaBlaster (09.04.09 19:22)

Достаточно таблицы DEVICE_ID DEVICE_TYPE DEVICE_NAME ПОЛЕ С ПРОЧЕЙ ЛАБУДОЙ ХАРАКТЕРНОЙ ДЛЯ КОНКРЕТНОГО ДЕВАЙСА

← →
DVM © (2009-04-09 19:41) [2]

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

← →
palva © (2009-04-09 19:45) [3]

Если объем данных большой, (а для чего тогда БД?), то однозначно третий.
Почему? Потому что это стандартный способ, который понятен и привычен специалистам по БД, и данные в этом виде все движки SQL умеют эффективно обрабатывать. Только надо еще правильно создать индексы.
А "выборка не самая тривиальная", это зря. Хотя бы год попишете запросы - и будете писать такое автоматом.

← →
oxffff © (2009-04-09 19:46) [4]

> DynaBlaster (09.04.09 19:22)

google отношение супертип подтип реляционные СУБД

← →
oxffff © (2009-04-09 19:47) [5]

или с точки зрения корректности проектирования это вариант 3.

← →
oxffff © (2009-04-09 19:47) [6]

или с точки зрения корректности проектирования это вариант 3.

← →
DVM © (2009-04-09 19:52) [7]

Если будут постоянно добавляться новые типы устройств 3 вариант станет очень неудобен.

← →
Ega23 © (2009-04-09 19:58) [8]

> Если будут постоянно добавляться новые типы устройств 3
> вариант станет очень неудобен.

Зависит от СУБД. В Postgres с его наследованием таблиц - очень даже удобен.

← →
palva © (2009-04-09 20:08) [9]

А если так сделать:
Первая: ID, Name, TypeDevice
Вторая: ID, характеристика значение
Одной строке первой таблицы соответствуют несколько строк второй.
Только теперь отсутствует информация о том, какие характеристики присутствуют в том или ином типе устройства. Кроме того все значения характеристик должны иметь один тип, например, строковый.

← →
Игорь Шевченко © (2009-04-09 20:09) [10]

> Допустим, один тип обладает характеристиками: "a", "b" и
> "c".
> Второй тип обладает: "d", "e" и "f".
>
> Информацию об устройствах нужно хранить в БД, при этом у
> каждого устройства конкретного должен быть свой индивидуальный
> ID, также у устройств есть имена. Как лучше это делать,
> в каких таблицах?

1. (DEV_TYPE_ID, прочая лабуда типа)

2. (DEV_ID, DEV_TYPE_ID, DEV_NAME, прочая лабуда устройства)

3. (DEV_TYPE_ID, имя_характеристики, прочая лабуда характеристики)

← →
palva © (2009-04-09 20:10) [11]

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

← →
oxffff © (2009-04-09 20:16) [12]

> palva © (09.04.09 20:08) [9]

К вашему решению добавить

Для очень крутых ребят

1. еще есть метатаблица с описание характеристик для каждого типа.
2. Добавить таблицы

характеристика_ID, тип значение целое
характеристика_ID, тип varchar
характеристика_ID, тип blob
.....
3. в вашу таблицу
Вторая: ID, характеристика значение добавить
преобразовать к виду

характеристика_ID,ID, характеристика_type_id, value_type
4. Где value_type справочник поддерживаемых типов из пункта 2.

← →
oxffff © (2009-04-09 20:19) [13]

> oxffff © (09.04.09 20:16) [12]

Это решения для характеристик плавающего типа. То есть можем задать значение одной и той же характеристики разными типами.

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

← →
int64 (2009-04-09 20:21) [14]

palva © (09.04.09 20:08) [9]
Игорь Шевченко © (09.04.09 20:09) [10]
oxffff © (09.04.09 20:16) [12]

Это предполагает, что тип характеристики может быть сущностью.
Автор такого не говорил.

← →
oxffff © (2009-04-09 20:23) [15]

> int64 (09.04.09 20:21) [14]

На этом форуме обычно мало интересует то, что спрашивает автор.
:)

← →
Игорь Шевченко © (2009-04-09 20:28) [16]

int64 (09.04.09 20:21) [14]

> Это предполагает, что тип характеристики может быть сущностью.

Что есть характеристика, если не сущность, тем более, в данном контексте ?

← →
palva © (2009-04-09 20:43) [17]

> Автор такого не говорил.

Автор неосторожно проронил: "Возможно, какие-то другие варианты?"

← →
int64 (2009-04-09 21:03) [18]

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

← →
Игорь Шевченко © (2009-04-09 21:09) [19]

> Я мел ввиду, что характеристика может и не быть простым
> аттрибутом

а какая собственно разница ?

← →
int64 (2009-04-09 21:48) [20]

Да нет разницы.
У одного девайса характкристики - лог счетчиков, у другого - цена и производитель.
Автор же не сказал, что логически характеристики можно представить одной сущностью. (хотя, наверно, это и подразумевал)
А физически что-угодно можно скрутить.

← →
Ping-Pong (2009-04-09 22:04) [21]

>>> Игорь Шевченко © (09.04.09 21:09) [19] а какая собственно разница ?

Давай все хранить в бинарном потоке, а?

← →
DynaBlaster (2009-04-10 12:31) [22]

> В последнем поле хранить набор всех остальных характеристик
> все вместе. Конечно, например, поиск по этим характеристикам
> будет затруднен, но скорее всего это в данном конкретном
> случае не понадобится.

интересная идея.. Типа как GET-параметры:

ParamName1=ParamValue1&ParamName2=ParamValue2&...

Но добавляется постоянный парсинг (по скорости это не критично). Не очень хороший подход, имхо, для БД, где один из смыслов это структурирование типов данных.

> Если объем данных большой, (а для чего тогда БД?),

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

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

> Если будут постоянно добавляться новые типы устройств 3
> вариант станет очень неудобен

да. Но варианты 1-2 будут не менее неудобными.

> Первая: ID, Name, TypeDevice
> Вторая: ID, характеристика значение
> Одной строке первой таблицы соответствуют несколько строк
> второй.
> Только теперь отсутствует информация о том, какие характеристики
> присутствуют в том или ином типе устройства. Кроме того
> все значения характеристик должны иметь один тип, например,
> строковый.

интересно, но мне кажется не для этого случая.

> 1. (DEV_TYPE_ID, прочая лабуда типа)
> 2. (DEV_ID, DEV_TYPE_ID, DEV_NAME, прочая лабуда устройства)
> 3. (DEV_TYPE_ID, имя_характеристики, прочая лабуда характеристики)

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

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

> Пример: у одного из типов устройств есть характеристика
> порт, она может изменяться в большом диапазоне 1-65 тысяч,
> а у других устройств данной характеристики просто нету

3. (DEV_TYPE_ID, имя_характеристики, прочая лабуда характеристики)

значит для типа DEV_TYPE_ID такой характеристики не будет у тех типов устройств, где порт отсутствует.

У меня реализуется такой вариант:
1 "сборная" таблица устройств ID, Name, TYPE_ID
2 Таблица типов устройств TTPE_ID, TYPE_NAME, TYPE_TABLE NAME
3 собственно сами таблицы по одной для каждого вида устройств ID, property1, property2..., причём имя каждой таблицы записано во второй таблице как TYPE_TABLE NAME.
Такая структура даёт универасльное решение при заранее неизвестном количестве типов устройств и их характеристик.
Таблица под новый тип создаётся автоматически с указанным набором свойств.
Проблем с уникальностью ID в разных таблицах нет в принципе, используется один генератор для всех таблиц.
ИМХО просто и гениально. :)

> [0] DynaBlaster (09.04.09 19:22)

Прочитал по диагонали пунктиром, сори если повторю кого то.

Я бы попробовал реализовать так.
1. Типы устройств(ИД, Имя, все остальные дела)
2. Характеристика (ИД, Тип_ИД, Имя, все остальные дела)
3. Устройства (ИД, Имя, Тип_ИД, все остальные дела)
4. Характеристики устройств (ИД, Устройство_ИД, Характеристика_ИД, значение)

Sergey13 © (10.04.09 15:45) [25]

Характеристика есть свойство типа, насколько я понял.
То есть, тип устройства уже определяет набор его характеристик

← →
Городской Шаман (2009-04-10 18:33) [27]

> DynaBlaster (09.04.09 19:22)

Как вариант - в записи хранить общее описание устройства(идентичное для всех, по типу название, тип и прочее) и дополнительное поле типа blob в котором хранить xml-описание конкретных характеристик устройства.

← →
kaif (2009-04-10 18:45) [28]

Из предложенных трех вариантов третий - наилучший.

Добавлю еще один вариант. С наследованием.

Допустим имеются два вида устройств, у которых есть общие параметры (a,b). То есть два вида устройств характеризуются так:
a,b,c,d
a,b,e,f,g

Тогда делаем 4 таблицы:

id, class_id, name
id, a, b
id, c, d
id, e, f, g

Для созания экземпляра устройства нужно добавить запись с одинаковым id в 3 таблицы.
Это полная нормализация.
Предельная.
У меня так устроены справочники в финансовой системе Allegro.
Удобно, если поле name - сборное наименование. И способ сборки зависит от класса. При добавлении устройства первого типа name собирается из параметров a,b,c,d, а при добавлении устройства второго типа - из a,b,e,f,g. У меня сборка имен происходит автоматически при помощи хранимых процедур, тексты которых также генерируются автоматически на основе "шаблонов построения имен", которые у каждого класса свои.

При такой организации если нужен просто список устройств, можно вывести какие-то записи из первой таблицы. В первой таблице можно искать в поле name и при помощи запросов с использованием LIKE. Если же нужно строго сгруппировать по каким-то полям, то тогда уже делаются объединения 3 таблиц и мы получаем полную таблицу для отдельно взятого класса.

Допустим, у все электробытовые устройства имеют "потребляемую мощность". Но лишь стиральные машины имеют "частоту оборотов отжима". В случае 3 таблиц (как в сабже) не ясно, где должно находиться поле "потребляемая мощность". В случае же с наследованием (как в моих справочниках) это поле должно находиться в таблице собственных атрибутов "класса предка" для всех устройств, наследующих этому классу.
Условно так, например:

1. Устройства(id, calss_id, name)
2. Бытовые приборы(id, "потребляемая мощность")
3. Телевизоры(id, "размер экрана", "разрешение по вертикали", "разрешение по горизонтали")
4. Стиральные машины

Чтобы выбрать все атрибуты телевизоров, объединяем таблицы 1,2,3
А чтобы выбрать все стиралки, объединяем талицы 1,2,4
А чтобы найти все приборы, потребляющие менее 500 ватт, объединяем лишь таблицы 1 и 2 по условию "потребляемая мощность" < 500, и смотрим поле name.

Четвертая нормальная форма. Если не пятая. :)
Для фанатов нормализации, так сказать.

← →
kaif (2009-04-10 19:04) [29]

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

То есть отдельные таблицы с собственнымим атрибутами и первую денормализацию: "таблицу-реестр ID всех объектов". Вот сборку наименований в "реестре" рекомендую всячески. А наследование (хотя я его иногда и применял) редко оправдывает затраты. Так как мало кому бывают нужны запросы вида "все бытовые приборы, мощностью до 500 W.

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

1. Атомарные спрачоники низшего уровня (Страны, Города, единицы измерения, стандартные литражи бутылок, стандартные разрешения экранов телевизоров, и прочее), в основном такие справочники имеют структуру id, code, name.

2. Строгие справочники постоянных объектов (в данном случае - устройств), в основном содержащие ссылки на id атомарных справочников и имеющие множество полей (как правило до 10).

3. Реестр для постоянных объектов - денормализация строгих справочников в простое поле name (сборное наименование в виде строки), имеет обычно id, class_id, name.

4. Накопители для данных (заказы, продажи и т.п.). Обычно это двухуровневые связки таблиц Master-Detail, опирающиеся на атомарные справочники и справочники постоянных объектов. Часто имеют кучу полей. Нужных и не очень.

5. Денормализованные реестры для накопителей (журналы проводок, журналы документов и т.п.). Их следует тщательно продумывать.

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

← →
b z (2009-04-11 11:33) [30]

← →
DynaBlaster (2009-04-11 11:43) [31]

Игорь Шевченко © (10.04.09 13:11) [23]
3. (DEV_TYPE_ID, имя_характеристики, прочая лабуда характеристики)

значит для типа DEV_TYPE_ID такой характеристики не будет у тех типов устройств, где порт отсутствует.

ну это понятно.
Непонятно, где в такой структуре писать само значение порта конкретного устройства?

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

>3. (DEV_TYPE_ID, имя_характеристики, прочая лабуда характеристики)

а

>3. (DEV_ID, DEV_TYPE_ID, имя_характеристики, прочая лабуда характеристики)

Ну а на самом деле DEV_TYPE_ID тогда избыточен и:

>3. (DEV_ID, имя_характеристики, прочая лабуда характеристики)

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

kaif, спасибо, я понял.

Городской Шаман (10.04.09 18:33) [27]
Как вариант - в записи хранить общее описание устройства(идентичное для всех, по типу название, тип и прочее) и дополнительное поле типа blob в котором хранить xml-описание конкретных характеристик устройства

ну это уже предлагал DVM в [2]

← →
kaif (2009-04-11 12:11) [32]

решение Sergey13 © (10.04.09 15:45) [25] хорошо, если характеристик немного. Если их, к примеру 20 в каком-то классе, то для того чтобы получить "полную картину" нужен запрос с 20-кратным объединением. Это может привести к тормозу.

И это было бы еще ничего, если бы не проблема NULL-полей.

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

Допустим какой-то параметр отсутствует (строка удалена). Если запросы устроены как внутренние объединения, устройство просто будет потеряно. А если запросы будут устроены как внешние объединения, то велик риск других неприятностей. Допустим, отсутствует параметр "Масса, кг" у какого-то устройства. А приложение собирается просуммировать вес партии товара для отправки курьерской службой. При внешнем объединении под агрегатной функцией SUM() окажется значение NULL и результат будет некорректным либо сервер вообще сообщит об ошибке.

Следовательно к разработчику приложений предъявляется куча требований при подобном "вертикальном" хранении:

1. Запрет на внутренние объединения
2. Принудительное использование проверки на NULL во всех агрегатах. Наподобие SUM(CASE MYFIELD IS NULL THEN 0 ELSE MYFIELD END)

Теперь рассмотрим запрос, в котором в условии WHERE нужно обеспечить устройства "с массой менее 0,5 кг". В этом случае проблема NULL потребует соблюдение условия 2. не под агрегатом, а в условии WHERE. Однако и этого недостаточно. Так как если мы использовали условие 2. и нашли все устройства, у которых масса менее 0,5 кг таким способом, то в набор попадут как те устройства, у которых масса менее 0, 5 кг, так и те, у которых масса отсутствует вообще (нет такой записи). И каждый раз придется об этом думать. И требовать, чтобы пользователь различал "отсутствие массы" от "массы 0". Либо нужно в триггерах обеспечить проверку со сканированием вплоть до 20 строк, что делает систему сильно платформозависимой и вовсе не столь простой, как она кажется на первый взгляд.

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

Проектирование БД Найти похожие ветки