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.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 inner join Teams t on pm.MatchId = t.MatchId inner join PlayerTeams pt on t.Id = pt.TeamId inner join Players p on pt.PlayerId = p.Id where p.Id != 1 order by pm.CompletionDate desc