Entity Framework Code First Softball Stats Database Schema

Question

I am creating a website for my buddies and I to track all of our offensive softball stats. I am getting tired of maintaining a huge excel spreadsheet and emailing it out every week, so a website is the way to go.

I've created the DB a few times but I want to make sure I am making this thing right without duplicating data. Here is what I have for models:

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string NickName { get; set; }
}

public class Player : Person
{
    public int Id { get; set; }

    public virtual ICollection<Team> Teams { get; set; }
}

public class Manager : Person
{
    public int ID { get; set; }

    public ICollection<Team> Teams { get; set; }
}

public class Game
{

    public int ID { get; set; }
    public int TeamID { get; set; }
    public String Opponent { get; set }
    public DateTime? GameDate { get; set; }
    public string Score { get; set; }
    public Results Result { get; set; }

    public virtual Team Team { get; set; }
    public virtual ICollection<Stat> Stats { get; set; }
}

public class League
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Team> Teams { get; set; }
}

public enum Results
{
    W, L
}

public class Stat
{
    public int ID { get; set; }
    public int PlayerID { get; set; }
    public int GameID { get; set; }

    public virtual Player Player { get; set; }
    public virtual Game Game { get; set; }

    public int? BattingOrder { get; set; }
    public int? PA { get; set; }
    public int? Single { get; set; }
    public int? Double { get; set; }
    public int? Triple { get; set; }
    public int? HR { get; set; }
    public int? RBI { get; set; }
    public int? BB { get; set; }
    public int? SAC { get; set; }
    public int? RBoE { get; set; }
    public int? R { get; set; }

    public int? H { get { return Single + Double + Triple + HR; } }
    public int? AB { get { return PA - (BB + SAC + RBoE); } }
    public double? BA { get { return Math.Round(((double)H / (double)AB), 3); } }
    public int? TB { get { return Single + 2 * Double + 3 * Triple + 4 * HR; } }
    public double? SLG { get { return Math.Round(((double)TB / (double)AB), 3); } }
    public double? OBP { get { return Math.Round(((double)(H + BB) / (double)(AB + BB + SAC)), 3); } }
    public double? TBP { get { return Math.Round(((double)(H + BB) / (double)(AB + BB)), 3); } }
    public double? RC { get { return Math.Round(((double)OBP * (double)TB), 3); } }
}

public class Team
{
    public int ID { get; set; }
    public string Name { get; set; }
    public WeekNights WeekNight { get; set; }
    public int? Year { get; set; }

    public int ManagerID { get; set; }
    public virtual Manager Manager { get; set; }
    public virtual League League { get; set; }
    public virtual ICollection<Game> Games { get; set; }
}

public enum WeekNights
{
    Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
}

The 1-1 relationships aren't an issue, it's relating players to a team and a game and linking the stats that's hanging me up. If anyone has any advice or better, a schema drawing, that would be awesome.

Also, is it better to have Angular calculate the 'calculated' stats on the front end instead of trying to save them in the database? I didn't know how much that would slow down the sight calculating the stats every time you click a new player or game.

Answer 1

It depends on how you are planning on aggregating the stats.

Right now you have fields in your stat table that appear to really only apply when they are associated with a specific entity (player or team). For example, what does RBI on a stats record associated with a team mean?

I would avoid a design where a field like RBI means different things depending on what it's associated with. I'd personally prefer something like RBI to be only associated with a player.

In the case you do want to generate stats like "give me all RBI for the game" too and store it in the same field, you can use table per hierarchy and make entities TeamStats and PlayerStats based on a discriminator, and have RBI on the base class. I will caution that this approach is more difficult to set up and can lead to difficulty when maintaining it.

Another option is to track stats by player and compute team stats/game stats on demand (maybe via a stored proc, etc). The drawback there is that if doing so becomes computationally expensive then you have to worry about staging the data and dealing with possibly out-of-date data.

I would recommend setting up the following relationship structures:

• Players belong to Teams
• Games have their own Id and 2 Team FKs (so two teams can play multiple times)
• A Stat is the stats for a player for 1 game, that has a composite PK consisting of Player Id and Game Id (I think this is no longer a hassle with EF).

The stats-per-player-per-game approach will allow you the most flexibility but also potentially the most on-demand computational overhead, but I'd start there until you start experiencing bottlenecks.

Since you're going to be doing decimal computations I'd avoid float and go with decimal to avoid potential loss of precision.

As for how to present the computed stats, I'd use stored procedures/functions whose results get mapped to web API models so the data is immutable as soon as you get it from the database. If that becomes annoying or too complex, you can also go the route of querying and aggregating/computing the stats in a middle application layer. I would avoid doing any computations on the front end.