Wrestling simulator Entity Framework / database design

I'm struggling to come up with a database design.

Scenario: I'm creating a very basic wrestling simulator game.

I have the following model classes:

Wrestler

public class Wrestler
{
    public int WrestlerId { get; set; }
    public string Name { get; set; }
    public int Overall { get; set; }
    public string Finisher { get; set; }
    public virtual ICollection<Match> Matches { get; set; }
}

Promotion

public class Promotion
{
    public int PromotionId { get; set; }
    public string Name { get; set; }
    public decimal Budget { get; set; }
    public string Size { get; set; }
}

Show

public class Show
{
    public int ShowId { get; set; }
    public string Name { get; set; }
    public int PromotionId {get; set;}
    public virtual Promotion Promotion { get; set; }
}

Match

public class Match
{
    public int MatchId { get; set; }
    public string MatchType { get; set; }
    public int ShowId { get; set; }
    public virtual Show Show { get; set; }
    public virtual ICollection<Wrestler> Wrestlers { get; set; }
}

Wrestler Match

public class WrestlerMatch
{
    public virtual int WrestlerId { get; set; }
    public virtual int MatchId { get; set; }
    public virtual Wrestler Wrestler { get; set; }
    public virtual Match Match { get; set; }
}

For a match, I've created a many-to-many table, called WrestlerMatch, which lists the Id of the Wrestler and the Match they're assigned to compete in.

However, I'm wondering how do I map out the winners and losers of the match?

Is there another table I need to solve this issue, for example:

(My description may be incorrect below)

One option would be to add something like bool IsWinner { get; set; } to WrestlerMatch.

This structure can work for both 1 on 1 and team matches, though it would be a bit manual to manage the IsWinner for team matches where IsWinner would be set on 2 or more entries.

Alternatively you could introduce something like a "Side" or "Corner" to a Match which would track which side in a match wins, then associate one or more wrestlers to each side.

Then you'd have:

Match -> Corners (with IsWinner) -> CornerWrestlers -> Wrestler

The business logic would need to enforce how many corners can be in a match, and how many wrestlers can be in a corner, (ensuring equal count, no doubling up of wrestlers within a match, etc.) This would support 1v1, 2v2, 4v4, 2v2v2, 2v2v2v2, etc.

Some quick tips about EF and navigation properties to help avoid some headaches:

When using navigation properties, I recommend not declaring the FK fields in the entities, instead using Map(x => x.MapKey()) (EF6) or Shadow Properties (EF Core). For example:

public class Show
{
    public int ShowId { get; set; }
    public string Name { get; set; }
    public virtual Promotion Promotion { get; set; }
}

public class ShowConfiguration : EntityTypeConfiguration<Show>
{
    public ShowConfiguration()
    {
        ToTable("Shows");
        HasKey(x => x.ShowId)
            .Property(x => x.ShowId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

       HasRequired(x => x.Promotion)
           .WithMany()
           .Map(x => x.MapKey("PromotionId");
    }
}

The issue that can occur with having both a Promotion and PromotionId is the assumption that both will always be in sync. To change a show's promotion you could replace the Promotion reference and/or update the PromotionId. The correct way is to update the navigation property, however the PromotionId won't be updated automatically until after SaveChanges is called. This can leave openings for bugs for any code assuming PromotionId is always valid and uses show.PromotionId vs. show.Promotion.PromotionId.

EF fully supports bi-directional navigation properties (Wrestler has Matches and a Match has Wrestlers) though it is typically easier to manage single direction references wherever possible and save bi-directional references for where you truly need them. You can always query/filter data from a top-level entity like a Match and drill down to Wrestlers in the context of that match without needing "Matches" on a Wrestler.

For example, if I have a Wrestler, a Match containing Corners and Wrestler is assigned to a Corner, My DbContext may have Wrestlers so that I can manage my Wrestler pool, but when it comes to reviewing a match, or seeing my Wrestler's match performance, Wrestler doesn't need Corners etc. I can access that information via the Match:

var wrestlerWinCount = context.Matches
    .Where(m => m.Corners
       .Where(c=> c.IsWinner)
       .Any(c => c.Wrestlers.Any(w => w.WrestlerId == wrestlerId)))
    .Count();

Bi-directional references would allow:

var wrestlerWinCount = context.Wrestlers
    .Where(w => w.WrestlerId == wrestlerId)
    .SelectMany(w => w.Corners)
    .Where(c => c.IsWinner)
    .Count();

The issue with dealing with bi-directional references is that when editing relationships that are bi-directional, you need to update both sides. For instance for a match to substitute "Iggy the Ugly" with "Randy the Rugged" you would need to remove the "Corner" from Wrestler Iggy and add it to Randy, then also remove Iggy from that Corner Wrestlers collection, and add Randy. Forgetting to update one side of the bi-directional relationships can lead to update errors or unexpected data state at the end. It's generally simpler to rely on 1-direction references as much as possible.

Edit: Mapping a Match to Corner with single-direction references, from Match to Corner:

public MatchConfiguration()
{
    ToTable("Matches");
    HasKey(x => x.MatchId)
        .Property(x => x.MatchId)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    HasMany(x => x.Corners)
       .WithRequired()
       .Map(x => x.MapKey("MatchId"));
}

One match has several Corners, the code logic needs to enforce a valid minimum and maximum. WithRequired() ensures a Corner needs a MatchId, but does not reference a Match entity. Map(x => x.MapKey("MatchId")) tells the mapping to look for a MatchId column in the Corners table to link to the Match.

The code logic still needs to guard against any possibility that multiple corners could end up set to IsWinner = True. IMO the best practice to help avoid problems like this is to adopt a DDD approach with actions against entities rather than code accessing setters directly in entities. If the entities have protected/internal setters and instead use methods for actions to update state (I.e. at a Match level have a AssignWinner(cornerId) method, that method becomes the only place IsWinner is set, and can validate the Corner is part of the match and that all other corners IsWinner is false. Just something to consider to hopefully avoid data state issues /w EF or other ORMs.

Edit #2: Match, Corner, Wrestler without bi-directional references (and a shadow CornerWrestler joining table)

Entities:

public class Match
{
   public int MatchId { get; set; }
   // other match related fields.

   public virtual ICollection<Corner> Corners { get; set; }
}

public class Corner
{
    public int CornerId { get; set; }
    public bool IsWinner { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Wrestler> Wrestlers { get; set; }
}

public class Wrestler
{
    public int WrestlerId { get; set; }
    public string Name { get; set; }
    // other wrestler specific fields...
}

For configuration, to let EF know how these are related:

public MatchConfiguration()
{
    ToTable("Matches");
    HasKey(x => x.MatchId)
        .Property(x => x.MatchId)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    HasMany(x => x.Corners)
       .WithRequired()
       .Map(x => x.MapKey("MatchId"));
}

public CornerConfiguration()
{
    ToTable("Corners");
    HasKey(x => x.CornerId)
        .Property(x => x.CornerId)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    HasMany(x => x.Wrestlers)
       .WithMany()
       .Map(x => 
       {
           x.MapLeftKey("CornerId");
           x.MapRightKey("WrestlerId");
           x.ToTable("CornerWrestlers");
       });
}

public WrestlerConfiguration()
{
    ToTable("Wrestlers");
    HasKey(x => x.WrestlerId)
        .Property(x => x.WrestlerId)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

}

Note that there are no bi-directional references, no FK properties in the entities, nor a CornerWrestler entity for the CornerWrestler table. From a corner you are dealing with a collection of Wrestlers. EF manages the many-to-many table behind the scenes. This is possible when the CornerWrestler table contains nothing but CornerId and WrestlerId as a composite PK. This is similar to how something like a WrestlerMatch join table could have worked, except that requires mapping a WrestlerMatch entity and using that in the collections (instead of Wrestlers) if you want to support tracking an IsWinner in that table/entity. EF can map a joining table if that table contains nothing more than the reference FKs. (AFAIK this is only supported in EF6, EF Core still hasn't implemented this and requires joining entities.) Mapping so a corner entity deals directly with wrestlers makes accessing wrestlers simple and intuitive.

To get all wrestlers in a match:

var wrestlers = match.Corners.SelectMany(c => c.Wrestlers);

If you map out the CornerWrestler entity, then accessing a wrestler from a match is a bit more round-about...

var wrestlers = match.Corners
    .SelectMany(c => c.CornerWrestlers.Select(cw => cw.Wrestler));

I.e. you'd always have to navigate through a CornerWrestler (or WrestlerMatch) to get to the wrestler.

Anyhow it may seem a bit different to the setup you were starting with, but have a read through on 1-to-many vs. many-to-many relationship configuration with EF and the different configuration options. It can allow you to arrange things in a more intuitive way and let EF work out how the data structure works behind the scenes rather than relying on an entity structure that mimics the relational data structure. (Leverage the "Mapper" in the OR"M")