Database design question

[K]

Just want to get some input on a design for a database/related project that I'm working on.

I have a table that contains user stats for a specific game (including some that are calculated before insert via a trigger).  We'll call it `game_stats`.

Now, I want to be able to pull aggregate statistics (ie, career stats) for the user from the database as well.  Here are the options I came up with:

Create a table `career_stats` that is updated on every insert into `game_stats`.
Create an SQL function or view that calculates and returns the player's career stats.
Create a function outside of the database that performs the calculations on all the user's `game_stats` rows.


The first option is what I was planning on doing, but then I figured I'd also need to handle stats changing when a row is deleted, when a row is updated, etc, and it could be a major headache.

The second option doesn't have those problems, but it will require more calculations every time career stats are requiest from the DB.

The third option just kinda sucks.

If there's another (better) way to do it, let me know.

[MyndFyre]

I'd go with the first one, but to avoid the headache, you could treat it as a precalculated view.  In other words, don't push the data into the table, have the table pull the data.

This is how I first envisioned option 1:

Code Select Expand


CREATE PROCEDURE [AddGameStat]
  @PlayerID int, @GameID int, @Score int
AS
  INSERT INTO [game_stats] (PlayerID, GameID, Score) VALUES (@PlayerID, @GameID, @Score);
  DECLARE @prevScore AS int;
  SELECT @prevScore = Score FROM [career_stats] WHERE PlayerID = @PlayerID;
  UPDATE [career_stats] SET Score = (@prevScore + @Score);

  SELECT 'Return Value' = SCOPE_IDENTITY();
END

(btw, sorry if MySQL or whichever database server you're using uses different syntax, I'm used to SQL Server).

So anyway, you'd have that UPDATE [career_stats] in every one of your calls.

What I'm thinking is, rather than have that in each call, you black-box it and encapsulate it into its own stored proc.

Code Select Expand


CREATE PROCEDURE [UpdateCareerAggregates]
  @PlayerID int
AS
  DECLARE @scoreSum AS int;
  SELECT @scoreSum = SUM(Score) FROM [game_stats] WHERE PlayerID = @PlayerID;
  UPDATE [career_stats] SET Score = @scoreSum WHERE PlayerID = @PlayerID;
END

-- And now:
CREATE PROCEDURE [AddGameStat]
  @PlayerID int, @GameID int, @Score int
AS
  INSERT INTO [game_stats] (PlayerID, GameID, Score) VALUES (@PlayerID, @GameID, @Score);
  EXEC [UpdateCareerAggregates] @PlayerID;

  SELECT 'Return Value' = SCOPE_IDENTITY();
END


Then, all you need to do is add the EXEC [UpdateCareerAggregates] into the stored procedures that update your game_stats table.  This has the added bonus that if you change your aggregated values, you don't need to update your other stored procedures.

[Ersan]

"precalculated view" isn't any terminology I've ever heard of, they're just stored procedures - you could also use joins.

[MyndFyre]

"precalculated view" isn't any terminology I've ever heard of, they're just stored procedures - you could also use joins.

If you've ever used SQL Server, that's akin to a "View", or in Access, like a "Query".  I don't know what the object is called in MySQL.

I know that you don't really care, that you're just trolling, but this is clarification for the people who want to use this topic as a potential future reference.

[Ersan]

http://www.google.com/search?hl=en&q=%22precalculated+view%22

Nice.  Honestly I don't think you ever know what you're talking about...  But you're a mod so this post will get trashed and people will continue to believe your bullshit.

[Banana fanna fo fanna]

Ersan you are so retarded. Like...wow.

[Ersan]

Thanks, I value your opinion.