правильное объединение таблиц

[Int O'Stern]

Добрый день, уважаемые форумчане.
Прошу помочь в решении вопроса.
База спроектирована таким образом. У каждого пользователя есть набор параметров. У каждого параметра есть название. Напимер есть пользователь_1, у него параметры: цвет_волос,рост,вес,ориентация...
Те же параметры, но с другими значениями есть и у других пользователей.
Получаем три таблицы: таблица пользователей, таблица с описанием параметров и таблица значений параметров.
Т.е:
tbl_users:
user_id1, user_name1
user_id2, user_name2
...
tbl_custom_fields:
custom_id1, custom_name1,custom1_sortorder
custom_id2, custom_name2,custom2_sortorder
...
tbl_custom_values:
user_id1, option_id1, custom_value1000
user_id1, option_id2, custom_value1001
user_id2, option_id1, custom_value1002
user_id2, option_id2, custom_value1003
...
Вопрос. Как максимально быстро построить таблицу:
user1_name user1_id custom_value1000 custom_value1001 ...
user2_name user2_id custom_value1002 custom_value1003 ...
...

 

[nittis]

Не очень понятно, что требуется получить в конечном итоге?

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

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

Для просмотра ссылки Войди или Зарегистрируйся

 

[Int O'Stern]

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

Основная идея - сделать гибкую систему для работы с объектами с произвольным числом произвольных параметров. Например для структуры "юзер" в один момент времени заданы "кастомизабельные" поля "рост", "вес", "цвет_глаз". При расширении системы в таблицу "кастомфилдов" необходимо просто добавить один параметр, например "рост". Я ищу алгоритм, при помощи которого можно наиболее оптимально вынуть из базы обект и его характеристики.

Самый простой алгоритм выглядит так:
1. Согласно таблице кастомфилдов вынуть все custom_id в порядке сортировки ( количество запросов к БД: один)
2. Для каждого user_id вынуть значения кастомфилда с custom_id из п.1. (количество запросов к БД: количество кастомфилдов).

Таким образом для построения списка из 10 пользователей с 10 параметрами у каждого пользователя, необходимо произвести 10*(10+1) запрос к БД, что изначально неприемлемо.

 

[nittis]

Теперь гораздо яснее.

Для того чтобы выбрать подбное нужно всего два запроса.

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

2. На основе полученного списка формируем новый запрос (например на PHP), в котором участвуют сразу все отобранные поля. (C LEFT JOIN знакомы?)

В результате получаем разреженную таблицу

Что-то вроде этого (если у пользователя нет какого-то свойства, то на его месте будет стоять NULL)

User1 Val1 Val2 NULL
User2 Val3 NULL Val4 
User3 NULL NULL Val5

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

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

 

[Int O'Stern]

Теперь гораздо яснее.
Для того чтобы выбрать подбное нужно всего два запроса.

Nittis, спасибо.
Этот вариант мне кажется наиболее правильным. Первый запрос - анализ кастомфилдов и построение на этой основе строки запроса с джоинами для вытягивания из базы значений. И для каждого пользователя используется этот запрос. Получается, что количество запросов = (количество пользователей в списке + 1). Для списков с пейджингом и 10-20 обьектами на странице это уже вполне реально.

Изначально предлагалась еще другая структура:
tlm_custom_values:

user_id1 custom_value1 custom_value2... (по сути обычная матрица)
т.е. для добавления поля, необходимо было создавать новый столбец с именем custom_n в таблице. Я посчитал, что такой вариант не есть хорошо, если надо изменять структуру таблицы при добавлении нового поля.

---------- Post added at 12:08 ---------- Previous post was at 10:17 ----------

М-да. Вобщем не получился фокус. Выборка из 10 параметров для каждого юзера таким образом занмает порядка 6 сек на локалхосте.

SELECT client.id, client.manager_id, client.operator_id, custom1.value, custom2.value,custom3.value, custom4.value,custom5.value, custom6.value,custom7.value, custom8.value,custom9.value, custom10.value, custom11.value, custom12.value

FROM `client`
JOIN `client_custom_options` AS custom1 ON client.id = custom1.user_id
AND custom1.option_id = '25'
JOIN `client_custom_options` AS custom2 ON client.id = custom2.user_id
AND custom2.option_id = '26'
JOIN `client_custom_options` AS custom3 ON client.id = custom3.user_id
AND custom3.option_id = '63'
JOIN `client_custom_options` AS custom4 ON client.id = custom4.user_id
AND custom4.option_id = '64'
JOIN `client_custom_options` AS custom5 ON client.id = custom5.user_id
AND custom5.option_id = '65'
JOIN `client_custom_options` AS custom6 ON client.id = custom6.user_id
AND custom6.option_id = '66'
JOIN `client_custom_options` AS custom7 ON client.id = custom7.user_id
AND custom7.option_id = '67'
JOIN `client_custom_options` AS custom8 ON client.id = custom8.user_id
AND custom8.option_id = '68'
JOIN `client_custom_options` AS custom9 ON client.id = custom9.user_id
AND custom9.option_id = '69'
JOIN `client_custom_options` AS custom10 ON client.id = custom10.user_id
AND custom10.option_id = '70'

JOIN `client_custom_options` AS custom11 ON client.id = custom11.user_id
AND custom11.option_id = '74'
JOIN `client_custom_options` AS custom12 ON client.id = custom12.user_id
AND custom12.option_id = '75'

ORDER BY client.id DESC
LIMIT 10 , 20


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

 

[nittis]

А индексы присутвствуют?

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

 

[Int O'Stern]

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

Что имееттся ввиду под "тройками"?

 

[nittis]

Что имееттся ввиду под "тройками"?

Так я выше писал.

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

Т.е. в результате запроса получаем что-то вроде

User1 Option1 Val11
User1 Option2 Val12
User2 Option1 Val21
User2 Option3 Val23
User3 Option3 Val33

Получив эту выборку разбираем ее уже на PHP

 

[Int O'Stern]

Так я выше писал.
Т.е. в результате запроса получаем что-то вроде
User1 Option1 Val11
User1 Option2 Val12
User2 Option1 Val21
User2 Option3 Val23
User3 Option3 Val33
Получив эту выборку разбираем ее уже на PHP

Боюсь, что делать mysql_fetch_array для каждого параметра будет очень ресурсоемко.

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