CODEWITHSUNDEEP
c#sql
ebb
ebb

Reputation: 9377

SQL: Design database to reflect tournament design

I'm trying to make a simple tournament system, and now I've come to another wall... I have a Table named Teams - Each Team can participate in multiple matches.

I've thought about having a table named Matches which have 4 columns:

  • ID => Identifier
  • Team1ID => Reference to ID in Teams
  • Team2ID => Reference to ID in Teams
  • WinnerID => Same value as either Team1ID or Team2ID

WinnerID will then have a sql trigger that checks whether its the same value as Team1ID or Team2ID... If not it will rollback the transaction.

This approach seemed perfect in the start, until I realized that it broke several "best practice" rules, including the "repository per aggregate root" because it would require me to do:

Team myTeam1 = ... Get team1 ...
Team myTeam2 = ... Get team2 ...

Match myMatch = new Match { Team1 = myteam1, Team2 = myteam2; }

which is wrong since the Match entity is defined as a "child entity" for the Team entity, and should only be added through the Team entity.

Any suggestions about how to design my database properly to fit my needs?

Upvotes: 1

Views: 864

Answers (2)

Cade Roux
Cade Roux

Reputation: 89661

As far as the trigger, I would just use some constraints.

(Team1ID <> Team2ID)
AND
(WinnerID IS NULL OR Team1ID = WinnerID OR Team2ID = WinnerID)

probably all in one constraint.

As far as the domain model, I would not have thought you add a match through a team, but perhaps through a tournament instance: Match = Tournament.NewMatch(Team1, Team2) ;

Conceivably, there would be higher order tournament rules, like rounds, eliminations, etc.

Since it sounds like your Teams know their Cup, then to create a Team or retrieve it from the database, it would go through the Cup: Cup.NewTeam(TeamName) or Cup.LoadTeam(TeamID). It would still make sense then for Cup.NewMatch, since you certainly wouldn't want to have a match between teams in different Cups.

Upvotes: 1

CyberDude
CyberDude

Reputation: 8949

How would your model store a draw match? :)

Why not store the score in a match row and just determine the winner based on that?

Id, Team1Id, Team2Id, Team1PointsScored, Team2PointsScored

Upvotes: 0

Related Questions