Struktura bazy danych dla gry 2 na 2

Regularnie gram w grę 2 na 2 z 12 przyjaciółmi i chcę, aby baza danych śledziła graczy, zespoły, wyniki i gry z zamiarem stworzenia systemu rankingowego.

Ponieważ regularnie zmieniać zespoły Doszedłem z tabel players, teamsa gamesgdzie gry mają dwa zespoły (TEAM1 i team2) oraz zespoły składają się z dwóch graczy (Gracz1 i player2).

Powoduje to sporo problemów - na przykład, jeśli wybiorę dwóch graczy (nazwijmy ich A i B ) do wspólnej gry, muszę sprawdzić, czy istnieje już drużyna, w której Gracz 1 to A, a Gracz 2 to B lub Gracz 1 to B i Gracz 2 jest.

Kolumny gamesi winssą obecne zarówno na playersstole, jak i na teamsstole - ale dzieje się tak, ponieważ chcę zobaczyć zarówno liczbę gier wygranych przez graczy, ale także zgodność gracza w różnych drużynach (jak często gracz wygrywa, gdy współpracuje z inny konkretny gracz).

1. Tabela wyników (prawdopodobnie użyję systemu oceny Elo )
2. Strona statystyk dla każdego gracza z oceną, wygranymi, grami, statystykami ostatnich gier i z którymi graczami jest najbardziej kompatybilny.

Podejrzewam, że duża część tego narusza niektóre zasady normalizacji bazy danych, i chciałbym uzyskać sugestie dotyczące sposobu wdrożenia projektu bazy danych.

Są dwa problemy, które widzę w twoim bieżącym schemacie, jeden to problem polegający na sprawdzeniu dwóch pól w tabeli, aby ustalić, czy klucz złożony jest faktycznie duplikatem, a niektóre agregowane dane są zrolowane do poszczególnych tabel dla osobnych podmioty (szczególnie wygrane, ale także potencjalnie ocena gracza).

W przypadku pierwszego problemu nie ma żadnych sztuczek w bazie danych, dzięki którym jedno / dowolne pole klucza złożonego będzie traktowane w sposób LUB, którego szukasz, ale jeśli twoja baza danych obsługuje to, możesz utworzyć funkcję getPlayerTeams(player_id)do enkapsulacji Zapytanie.

(Możesz również utworzyć widok z odciskiem team_thumb obliczonym jako skrót sortowanych identyfikatorów graczy, aby każda kombinacja tych samych dwóch osób zawsze dawała ten sam odcisk palca, ale tutaj może być trochę więcej).

Jeśli chodzi o normalizację, rozważ oddzielenie bytów od wyników, które występują, używając team_resulttabeli do śledzenia wszystkich wyników dla danego zespołu. Nieco bardziej ekstremalna normalizacja wymagałaby również player_rating_histstołu zawierającego wszystkie zmiany oceny dla gracza. Ich obecna ocena to po prostu najnowsza data. Widok odtwarzacza można również wykorzystać do zawarcia najnowszej wartości w celu łatwego zapytania.

Proponowany schemat (przepraszam, brak schematu):

player
    id
    name
    created_on
    updated_on

player_rating_hist
    player_id (FK)
    rating
    rating_date

team
    id
    player1_id (FK)
    player2_id (FK)
    created_on
    updated_on

game
    id
    team1_id (FK)
    team2_id (FK)

team_game
    team_id (FK)
    game_id (FK)
    result
    score
    rating_change

team_rating_hist
    team_id (FK)
    rating
    rating_date

Zapytania:

--Results for the game, should only ever be two rows for any given game
SELECT * FROM team_game WHERE game_id = 101

--All results for a team
SELECT * FROM team_game WHERE team_id = 123456 

Ta struktura pozwala oddzielić podmioty „podstawowe” (graczy i drużyny) od „treści”, które pojawiają się w wyniku działania systemu w czasie, i oznacza, że ​​nie aktualizujesz stale jednej z tabel podstawowych o bieżącej ocenie, # wygranych itp. Są to wartości pochodne i należy je pobrać, uzyskując najnowszą ocenę, średnią ocenę COUNTwygranych lub strat itp. Jeśli system jest wystarczająco duży, możesz rozważyć wyodrębnienie takich zagregowanych danych do osobnego „magazynu” (nawet jeśli był to tylko oddzielny zestaw tabel w tej samej bazie danych) dla łatwiejszej analizy.