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

Тема в разделе "Базы данных", создана пользователем Int O'Stern, 2 ноя 2009.

Статус темы:
Закрыта.
Модераторы: latteo
  1. Int O'Stern

    Int O'Stern Создатель

    Регистр.:
    11 авг 2008
    Сообщения:
    34
    Симпатии:
    0
    Добрый день, уважаемые форумчане.
    Прошу помочь в решении вопроса.
    База спроектирована таким образом. У каждого пользователя есть набор параметров. У каждого параметра есть название. Напимер есть пользователь_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 ...
    ...
     
  2. nittis

    nittis Постоялец

    Регистр.:
    21 апр 2009
    Сообщения:
    68
    Симпатии:
    29
    Не очень понятно, что требуется получить в конечном итоге?

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

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

    http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
     
  3. Int O'Stern

    Int O'Stern Создатель

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

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

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

    nittis Постоялец

    Регистр.:
    21 апр 2009
    Сообщения:
    68
    Симпатии:
    29
    Теперь гораздо яснее.

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

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

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

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

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

    Код:
    User1 Val1 Val2 NULL
    User2 Val3 NULL Val4 
    User3 NULL NULL Val5  
    
    Можно поступить иначе. Выбрать одни запросом тройки "пользователь, имя свойства, значение", а дальнейший анализ провести вне БД.

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

    Int O'Stern Создатель

    Регистр.:
    11 авг 2008
    Сообщения:
    34
    Симпатии:
    0
    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


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

    nittis Постоялец

    Регистр.:
    21 апр 2009
    Сообщения:
    68
    Симпатии:
    29
    А индексы присутвствуют?

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

    Int O'Stern Создатель

    Регистр.:
    11 авг 2008
    Сообщения:
    34
    Симпатии:
    0
    Что имееттся ввиду под "тройками"?
     
  8. nittis

    nittis Постоялец

    Регистр.:
    21 апр 2009
    Сообщения:
    68
    Симпатии:
    29
    Так я выше писал.

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

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

    Получив эту выборку разбираем ее уже на PHP
     
  9. Int O'Stern

    Int O'Stern Создатель

    Регистр.:
    11 авг 2008
    Сообщения:
    34
    Симпатии:
    0
    Боюсь, что делать mysql_fetch_array для каждого параметра будет очень ресурсоемко.

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