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

1. Имеется производство неких деталей. Каждую деталь нужно измерить и занести результаты измерений в БД.

2. У каждой детали есть три измерения, L1, L2 и L3. Скажем, длина-ширина-высота, но это не важно – только для примера. Получается весьма простая таблица:

3. Проблема в том, что детали бывают разные. Некоторые имеют больше трех измерений. При этом, четвертое измерение L4 для какой-то будет диаметром отверстия, а для какой-то чем-то еще. А может быть еще и  L5 и так далее. Понятно, что наибольшей дуростью была бы попытка предсказать все возможные варианты и писать все в большую таблицу, оставляя пустые поля для тех деталей, для которых данные измерения просто не существуют.

4. Если решать задачу в лоб, надо составлять по еще одной таблице для каждого необязательного измерения и связывать таблицы между собой через ID. Но это значит, что появление в производстве каждой новой детали требует наращивания структуры базы.

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

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

Теперь мои вопросы. 
а) Каких гадостей можно ожидать от такой структуры?
б) Имеет ли смысл наиболее частые величины из второй таблицы перенести в первую, позволив, соответственно, некоторым полям оставаться незаполненными? В этом случае, каковы могут быть критерии, по которым оценивается где должен находиться параметр: в главной таблице или в таблице дополнительных параметров?

Спасибо. 

о, это же wiki: entity-attribute-value model 
"гадости" могут быть при относительно сложных запросах: большое количество inner join'ов. 

скажем так: не самым удобным. Если тебе надо искать только по параметру А, то никаких проблем. А вот если по параметру А и по В, или же по А и В или по С, то тут уже в такой модели будет непросто построить адекватный запрос.

1)Как ты уже обозначил - усложняются поисковые запросы 
2)При добавлении новых параметров пользователи, возможно, захотят отображать их значения в табличном представлении. Это приводит к тому, что запросы, возможно, придется строить динамически, не имея при том возможности влиять на план этих запросов. Мое мнение, пользователей лучше заведомо ограничить в этом их желании.
3)Может возникнуть необходимость использовать один из определенных пользователем параметров при реализации системной логики. Этого допускать нельзя. В случае возникновения такой необходимости, нужно расширять главную таблицу. Это, собственно, уж и есть ответ на твой вопрос б)

Это сообщение отредактировал(а) Zloxa - 29.10.2010, 15:39

skyboy, в том и дело, что поиск там если и потребуется, то только по ID детали. Вся база - просто производственный лог. Информация извлекается если пришла рекламация от потребителя или проблема возникла. Никому никогда не понадобится выборка вроде "Все детали с размером  L2 от 1 до 2 мм".

Это сообщение отредактировал(а) _Y_ - 29.10.2010, 15:26

нет, это один из ее вырожденных вариантов - UDA - user defined attrs.
В нем вырождено понятие энтиити, и, в связи с этим, уходит целый ряд недостатков модели EAV.

Это сообщение отредактировал(а) Zloxa - 29.10.2010, 15:36

 Именно это и предполагается. SQL пользователю доступен не будет. Будут жестко зашитые в интерфейсе простые варианты выбора: Найти характеристики такой-то детали; найти характеристики деталей такого-то типа выпущенных в такой-то период (даты, типы деталей и т.д. в отдельных таблицах, понятное дело. В моем описании я дал только то, что относится к самому моему вопросу).

 От этого я не понял к сожалению. Что значит "использование параметра для реалозации системной логики"?

в смысле - "вырождено"? а "главная" таблица с "основными измерениями" - это не entity?
можешь подтолкнуть в нужном направлении?

Читай "в коде приложения". 
Т.е. когда значение используется не только для принятия какого либо решения пользователем(человеком), но и для принятия решения какого то программного модуля.
Не стоит хардкодить эти самые UDA. Т.е. если у тебе в коде придется использовать чтото вроде "if  get_udaval_by_udaid(5)= 3 then бла else блым", правильнее расширить атрибуты основной таблицы.

Добавлено через 1 минуту и 15 секунд

она не мета сущность. EAV поразумевает ко всему еще и динамическое создание ентитей.

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

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

 немного утрировано, но в корне верно. 

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

Если максимальное количество параметров заранее известно, то можно сделать примерно так:

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

В общем, как я понял, в моих изначальных рассуждениях особых дефектов нет. База функции свои исполнять будет. 

Помимо прямых советов, я теперь знаю слова "entity-attribute-value mode" и "UDA - user defined attrs". Скажу их клиенту. Ему приятно будет чувствовать высокотехнологичность задачи, которую для него решаем  

Спасибо большое! Закрываю тему.