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 entit开发者_JAVA百科y" 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?

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.

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