0

Note: this section is a little long-winded, so feel free to just read the bolded summary below.

For example, take the schema below. Could the 2 "Statistic" tables at the bottom be normalized further?

My initial reaction is to feel that it could be normalized further (having a Statistics table for each and every sport could result in a lot of tables), but if I were to do that, then what mechanism would enforce the requirements for each respective sport's statistics?

For example, a Baseball statistic MUST include values for TotalHits and TotalHomeRuns. If I keep the BaseballStatistic table the way it is, then I can set these columns to Not Null.

Thoughts? Maybe there is even a better way than this current schema.

My basic problem is that I need to associate different types (BaseballStatistic and FootballStatistic) to the same parent type (Game). Is there any way to do this other than what I have done here?

enter image description here

If it matters, I am using SQL Server.

user7560542
  • 527
  • 1
  • 5
  • 14
  • I don't understand why you need store aggregated records as tables, for those 'Statistics' values can be changed over the time and also a player can have different total 'homeruns' over different seasons. Just a single total is meaningless. There must be some log type of tables, like which player played on which sport, when, how many 'homeruns', etc. You may spend time how to design log table, one or multiple for each sport. – Wendy Apr 25 '17 at 20:25
  • No this is not a good design at all. Of course we don't want to design our tables to make queries easier but consider how absurdly difficult this would be to provide statistics for GameID of 11. You would have to use dynamic sql to know which table(s) to join to. To be honest, this type of database is a difficult one to design well. Your relationships are all screwy too. You have a *:* between game and player. You also logically would need a 1:1 between game and each statistic table. – Sean Lange Apr 25 '17 at 20:45
  • First of all, I apologize for the crudeness of this example. The idea presented with the Game and two Statistics tables is a very real need, however. One parent table with individual child tables that link to it. These child tables contain very different data, that must have rules enforced against it (e.g. not null columns). My current idea is to just merge the two Statistics tables into one table and accept that every row will always contain some null columns. Nulls only cost 1 bit, so it seems like a fair trade off. What would you do? – user7560542 Apr 25 '17 at 21:09
  • This question may have an answer here: http://stackoverflow.com/questions/29444171/database-what-design-to-point-on-two-possibilities/29446745#29446745 – Walter Mitty Apr 25 '17 at 22:52

0 Answers0