Проектирование БД для последующего статистического анализа

[ZHU35]

Подскажите как лучше организовать БД

смысл такой: есть химические элементы(ХЭ), из них можно сделать композицию(К) (кол-во ХМ в К может быть любое, как и их концентрация); эту композицию применяют для некоторого процесса - выходные характеристики процесса должны находится в таблице экспериментальных данных(ЭД).

Для статистического анализа нужно будет искать опыты в таблице ЭД по композиции(К), при этом концентрация ХЭ не важна, главное чтобы в композиции были только выбранные ХЭ.

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

Возможно у кого-то есть идеи как лучше организовать эту часть базы.

[evg_m]

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

В чем неудобство?

задача. найти эксперименты с кислородом и(или) водородом
и решение.

 select id from хэ -- "плохие" элементы
where name not in ('водород','кислород') 

select id_k from состав --композиции с плохими элементами
where id_хэ in ( select id from хэ -- "плохие" элементы
                 where name not in ('водород','кислород')
               )

ОТВЕТ будут выданы все эксперименты в которых был хотя бы один из перечисленных и не было никаких других. А также те где не было ни единого элемента.

select * from ЭД --правильные опыты   
where id_k not in ( select id_k from состав --композиции с плохими элементами
                    where id_хэ in ( select id from хэ  -- "плохие" элементы
                                     where name not in ('водород','кислород')
                                   ) 
                  )

[ZHU35]

неудобство в том что нужно делать очень много подзапросов

это лишь кусочек БД и на самом деле в таблицу с ЭД тянутся еще 2 ветки с такой структурой( ХЭ-СК-К---ЭД)

задача: найти эксперименты кислород + водород и концентрации этих ХЭ. и представить их в виде удобном для анализа.

хотелось бы чтобы возвращался результат как на картинке

мой запрос выглядит так
//с-концентрация

select id,b.с1,b.с2, v
from
(select id_k, sum(if(id_XЭ=1,с,null)) AS с1, sum(if(id_XЭ=2,с,null)) AS с2
from СК where id_k
in (select id_k from СК where id_k in ( select id_k from СК
where id_XЭ in ( 1)) and id_k in ( select id_k from СК
where id_XЭ in ( 2)) and id_k not in ( select id_k from СК
where id_XЭ not in ( 1,2)))
group by id_k) as b, ед
where ед.id_k=b.id_k

он находит все как нужно, но кол-во подзапросов пропорционально кол-ву ХЭ, а если еще 2 такие же ветки добавить, то на запрос будет страшно смотреть..

Итог: мне кажется что это совсем не оптимально, поэтому думаю, что стоит изменить структуру БД

[evg_m]

select id, x1.c as c1, x2.c as c2, v
from ck --берем "все" опыты (см. комментарий к where)
inner join --"из них отбираем" только те котрые включают водород вместе с его характеристиками(поле с)  для данного опыта(!)
          (select id_k, c, xэ.id from ck inner join хэ on хэ.id =ck.id_хэ where хэ.name='водород') as x1 on ck.id =x1.id_k
inner join --"из них отбираем" кислород
           (select id_k, c, хэ.id from ck inner join хэ on хэ.id =ck.id_хэ where name='кислород') as x2 on ck.id =x2.id_k
... --если надо из них выбираем по третьему и т.д. элементу
where --напоследок (по написанию, но не выполнению) исключаем все плохие (используют другие элементы)
ck.id_k not in ( select id_k from состав --композиции с плохими элементами
                       where id_хэ in ( select id from хэ  -- "плохие" элементы
                                        where name not in ('водород','кислород')
                                      ) 
                     )