Hey Guys,

Im designing a databse for my cricket club to record the results of matchs which will eventually be used in a website so that users can query the databse and view statistics.

My question - is there a best practice concerning what should be put in a table and what should be generated dynamically?

For example Every player will have their own statistics (avg run rate, high score etc) and every cricket season will also have its own statistics ( season avg run rate, season high score etc) and every match will also have its own statistics (game avg runs, game high score, etc).

So far I have included these statistics as attributes (columns) in my tables and thought that I would have a series of triggers that would fire and update the stats when new data is entered once a week after the game and then on the website when a user wishs to view a statistic instead of calculating them I could just do a simple select from a table.

Of course the alternative is that te statistics are calculated when the user requests them - this seems like it would be processor heavy to me.

Is there a best practice.

Thanks in advance.

Jizznit

This is one of the parts of a designer's juggling act... You need to figure out what makes sense in a given situation.

As a general rule, I don't store anything that can be calculated in a transactional database. If I enter data into the beastie, then I don't store computed data into it.

A datawarehouse is a very different story. Users don't enter anything into a DW, aggregate data arrives there via some flavor of scheduled job. I keep all kinds of computed data in a DW.

-PatP

It's along the same lines as the rules for normalization - Normalize the database to the greatest extent possible, only denormalize when necessary to improve performance.

Can't you just copy the design of a baseball database?