Объединение данных из нескольких таблиц в одной.

[RedLight]

Есть таблица с множеством пользователей (в примере с одним).

CREATE TABLE `users`(`id` INT, `name` TEXT);
INSERT `users`(`id`, `name`) VALUES (1, 'Vasily Pupkin');

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

CREATE TABLE `values_1`(`user_id` INT, `value_1` INT);
INSERT `values_1`(`user_id`, `value_1`) VALUES (1, 1);
 
CREATE TABLE `values_2`(`user_id` INT, `value_2` INT);
INSERT `values_2`(`user_id`, `value_2`) VALUES (1, 2);

Так можно получить фиксированное количество данных о пользователе.

SELECT `users`.`name`, `values_1`.`value_1`, `values_2`.`value_2` FROM `users` INNER JOIN `values_1`, `values_2`;

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

CREATE TABLE `values`(`user_id` INT, `value` INT, `index` INT);
INSERT `values`(`user_id`, `value`, `index`) VALUES (1, 1, 1), (1, 2, 2);

Как составить запрос получения данных о пользователе аналогичный предыдущему, но работающий с объединённой таблицей?

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

Сообщение отредактировано: RedLight - 18.10.23, 19:05

[Majestio]

Цитата RedLight @ 18.10.23, 17:03

но тогда у неё будет фиксированное число столбцов

Немного неверное проектирование. Лучше, и гораздо проще, поступить вот как, с учетом:

Цитата RedLight @ 18.10.23, 17:03

поскольку формат таблиц с данными одинаковый

Создаем таблицы:

Users - таблица пользователей с полями Id и UserName
Categories - таблица категорий данных с полями Id и CategoryName
User2Categorу - таблица данных по категориям с полями UserId, CategoryId, Data

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

[RedLight]

Здесь `Data` из `User2Categorу` заменён на `SkillLevel`.

CREATE TABLE `Users`(`Id` INT, `UserName` TEXT);
INSERT `Users`(`Id`, `UserName`) VALUES (1, 'Vasily Pupkin');
 
CREATE TABLE `Categories`(`Id` INT, `CategoryName` TEXT);
INSERT `Categories`(`Id`, `CategoryName`) VALUES (1, 'PHP Developers'), (2, 'English Speakers');
 
CREATE TABLE `User2Categorу`(`UserId` INT, `CategoryId` INT, `SkillLevel` INT);
INSERT `User2Categorу`(`UserId`, `CategoryId`, `SkillLevel`) VALUES (1, 1, 4), (1, 2, 3);

Правильно ли я понял предложенный пример?
Текстовые названия категорий в моём случае не нужны, хорошо бы их избежать.
Предложенная `User2Categorу` выглядит как моя таблица `values` и так же не ясно как сделать запрос на выборку пользователя с его данными из неё.
Имя пользователя, в принципе, тоже можно считать текстовыми данными, что бы рассмотреть пример с данными разных типов, так как ещё логин можно добавить, но пока не надо Таблица пользователей служебную информацию будет содержать, которая к данным не относится и всегда известны её количество и формат.

Сообщение отредактировано: RedLight - 19.10.23, 06:58

[Akina]

Вы задаёте сразу несколько вопросов.

Первый - как правильно хранить. На этот вопрос уже ответил Majestio - мы имеем типичную M:N связь между пользователями и и данными, и храним в формате, аналогичном EAV.

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

Если он должен быть как показано в первом посте, то решение простое - используем UNION ALL и литералы для фиксации того, из какой таблицы выбраны данные.

SELECT *
FROM main
JOIN (
    SELECT '1' AS source, * FROM slave1
    UNION ALL
    SELECT '2', * FROM slave2
    UNION ALL
    ....
    ) AS slave ON ...

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

[RedLight]

Похоже без изучения теории не обойтись, буду читать про EAV до полного просветления

[Majestio]

Цитата RedLight @ 19.10.23, 06:44

Правильно ли я понял предложенный пример?

В принципе - да. В приведенном тобою коде "заведены" две категории и один пользователь. И пользователю "привязаны" "скилы" по этим категориям.

Цитата RedLight @ 19.10.23, 06:44

Текстовые названия категорий в моём случае не нужны, хорошо бы их избежать.

Названия категорий - некий аналог названий столбцов "плоской" таблицы. Не нужно этого избегать Но если уж избегать по-взрослому, тогда и имена пользователей нужно удалить, хватит их ID

Цитата RedLight @ 19.10.23, 06:44

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

Если честно - проще пареной репы. Но нужно, чтобы ты подумал - это полезно на будущее. Ибо ...

Цитата RedLight @ 19.10.23, 07:23

Похоже без изучения теории не обойтись, буду читать про EAV до полного просветления

Это правильное решение!

[Akina]

Цитата RedLight @ 19.10.23, 07:23

Похоже без изучения теории не обойтись

Угу.

Цитата RedLight @ 19.10.23, 07:23

буду читать про EAV до полного просветления

Особое внимание - области применения и недостаткам, а также альтернативам.