Relational schema and query for multiplayer tabletop game

As an exercise, I was asked to design a database schema (for MS SQL Server) for a tabletop game. The requirements were simple: players compete in matches and there are specific match types (e.g. 1v1 or 2v2). A hypothetical web site that would make use of this database would include things like player match history, a scoreboard page, and a leaderboard page.

My initial design had some flaws and inconsistencies, but after thinking more on it, I came up with the following schema:

create table Players (
    Id int identity(1, 1) primary key,
    PlayerName nvarchar(50) not null
);

create table MatchTypes (
    Id tinyint primary key,
    Name nvarchar(50) not null
);

create table Matches (
    Id int identity(1, 1) primary key,
    MatchTypeId tinyint not null references MatchTypes(Id),
    IsComplete bit not null,
    CompletionDate datetime2 null
);

create table Teams (
    Id int identity(1, 1) primary key,
    MatchId int not null references Matches(Id),
    Score smallint not null,
    IsWinningTeam bit null
);

create table PlayerTeams (
    PlayerId int not null references Players(Id),
    TeamId int not null references Teams(Id)
);

Although the design is fairly simple, I'm sure I've made sub-optimal design decisions. What could be improved on?

In addition, I went ahead and wrote a SQL query to extract data for a hypothetical "Player Match History" page. The goal is something like the following:

Player: Foo
Matches:
Against Bar ... Won!
Against Quux ... Lost
With Baz, against Bar & Quux ... Lost
With Bar, against Quux & Baz ... Won!


Essentially, given a player, select all matches that the player participated in, and display them with opponents/teammates.

Here's the query, although I feel like it may be unnecessarily complex.

```
select pm.MatchId,
t.Id as TeamId,
p.Id as PlayerId,
p.PlayerName,
case when t.Id = pm.TeamId then 1 else 0 end as IsTeammate,
pm.Won
from (
select m.I

Good work on schema

Overall, I think your schema is clean, easy to follow, and makes sense (in the limited scope of your scenario). Granted the overarching or "whole" schema for something like this would be much larger, but if you kept following this sort of normalization it should be fine.

Aliases

I see at least 5 unclear aliases: p, t, m, pt, pm. Mr. Maintainer would have to read through your entire script to even grasp what these stand for. Your column names are short enough, I really don't think aliases are of much use, and they just make your script more confusing to review. Imagine this kind of notation used on 1000 lines of codes in a real business setting, this should drive my point home.

Common Table Expressions

You have a rather large subquery (which I will address next point) and in SQL Server and several other RDBMS, you can simplify the way it reads by using a CTE. So this:

from (
    select m.Id as MatchId,
        m.CompletionDate,
        t.Id as TeamId,
        t.IsWinningTeam as Won
    from Players p
    inner join PlayerTeams pt
        on p.Id = pt.PlayerId
    inner join Teams t
        on pt.TeamID = t.Id
    inner join Matches m
        on t.MatchId = m.Id
    where m.IsComplete = 1 and p.Id = 1
) pm

You could instead write, at the very beginning of your script:

with pm as (
        select m.Id as MatchId,
            m.CompletionDate,
            t.Id as TeamId,
            t.IsWinningTeam as Won
        from Players p
        inner join PlayerTeams pt
            on p.Id = pt.PlayerId
        inner join Teams t
            on pt.TeamID = t.Id
        inner join Matches m
            on t.MatchId = m.Id
        where m.IsComplete = 1 and p.Id = 1
    )
select /* bunch of work here */
from pm    -- reference CTE in main query as if it were a table

Redundancy

I noticed your subquery (or CTE) is joining some of the same stuff your main query is doing. Look:

from (
    select m.Id as MatchId,
        m.CompletionDate,
        t.Id as TeamId,
        t.IsWinningTeam as Won
    from Players p
    inner join PlayerTeams pt
        on p.Id = pt.PlayerId  -- (1)
    inner join Teams t
        on pt.TeamID = t.Id  -- (2)
    inner join Matches m
        on t.MatchId = m.Id
    where m.IsComplete = 1 and p.Id = 1
) pm
inner join Teams t
    on pm.MatchId = t.MatchId
inner join PlayerTeams pt
    on t.Id = pt.TeamId  -- (2)
inner join Players p
    on pt.PlayerId = p.Id  -- (1)

Best to avoid redundant operations, especially expensive join's, as it makes the execution slower. Only one of each should be needed in most cases.

Nitpick

This preferably should not be used: where p.Id != 1

Instead, use this: where p.Id <> 1

Although both work just the same, != is not ANSI-92 standard.

StackExchange Code Review Q#58718, answer score: 5