Какой паттерн использовать для БД many-to-many с одним столбцом-источником для двух столбцов many-to-many?

Question

[Василий]

Есть таблица с именами, для неё нужно соединить каждое имя с каждым именем, для записи уникальных значений для конкретной пары имён.
Получается, нужна таблица-посредник many to many:
id, имя1, имя2, значение

В обычном many to many требуется указывать в каком столбце таблицы-посредника будет имя- в имя1, или в имя2

А у меня один столбец-источник для двух столбцов many to many, и имя может встретиться как в первом столбце, так и во втором, невозможно указать где именно оно будет.

Подскажите паттерн для решения такой ситуации.

Comments

[Сергей Соловьев]

Можно описать, что эта таблица должна показывать. Какой в ней смысл?

[Василий]

Сергей Соловьев, личный винрейт между игроками. Точнее - винрейт name1 над name2
Кто на месте name1 решает первая встреча, по сути, это не важно.

в целом - нужно хранить у игроков личные очки, личный винрейт,
А так же винрейт всех игроков над всеми игроками. Вот с таблицей всех игроков над всеми игроками затык, не могу понять как это сделать, кроме как втупую сделать таблицу с именами не связанную ни с чем, либо таблицей one to many с дубликатами записей (сначала name1 - name2, потом name2-name1) но это прям очевидно жуткий костыль какой-то, хотелось бы узнать грамотное решение

Answer 1

[Сергей Соловьев]

Тут 2 варианта - через View, либо триггеры, т.к. очки меняются часто. Поэтому вариант с таблицей отдельной я отбрасываю. Триггеры привносят непонятки, поэтому тоже убираю.
View можно сделать так.

create view points_difference as
    (
        select first.names as name1, second.names as name2, first.points - second.points as points from user_points first 
            join user_points second 
                on first.id <> second.id
        
    );

В добавок можно сделать его materialized view, чтобы от лишних перерасчетов избавиться

Comments

[Василий]

Я дурак.
Прошу прощения, не подумал что вопрос можно понять двояко.
Ответ хороший, спасибо, узнал про представление, это мне тоже понадобится позже.

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

Забудьте про всё что я написал, вопрос ровно один:
Как адекватно сделать many to many от одного столбца. Всё.

[Василий]

Поправил вопрос, ещё раз прошу прощения, пет-проект, ещё не начал а уже крыша едет.

[Сергей Соловьев]

Василий, а в чем разница? ответ один и тот же, просто join нужно выполнять уже не на id, а на name.

[Василий]

Сергей Соловьев, ммм... не понял)))
Может сможете написать в тг https://t.me/snaiper04ek
Компенсирую неудобства эквивалентом стакана кофе)

[Сергей Соловьев]

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

[Василий]

Сергей Соловьев,
окей. Описываю фулл:
Создаю рейтинговую систему для файтинг сообщества дискорд, основой которой будут не очки, а винрейты.

Я планирую иметь на руках:
1 - таблицу с именами игроков с записями побед/поражений в общем
2 - таблицу побед/поражений конкретных игроков между друг другом -> из этой таблицы будет формироваться винрейт конкретного Григория над конкретным Михаилом, Вячеславом, Евгением, и т.д.

Вот мне нужны эти отдельные, частные победы/поражения, винрейты каждого игрока над каждым игроком, по причине того что винрейт 80% против новичка это хуже, чем 55% против супер опытного игрока, ценность их не одинакова.

Получается, я создаю таблицу имён
И хочу иметь ссылку на вторую таблицу, где будут записаны победы/поражения игрока против каждого с кем он когда-либо играл, и обновлять конечно же, когда он повторно с кем-то сыграет.

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

[Василий]

Да, винрейты можно отображать через view, это узнал из вашего ответа, это полезно.
Но сами результаты игр записываются напрямую, это реальные данные, а не представление данных.

[Сергей Соловьев]

create table matches
(
    id     int generated always as identity,
    winner int references user_points (id) not null,
    loser  int references user_points (id) not null,
    points int                             not null,
    constraint WINNER_NOT_LOSER check (winner <> loser)
);

[Сергей Соловьев]

Василий, используй ID пользователей, а не имена