Storing just the events without the context (the league or competition in which the game took place) is meaningless.

You want to know that Liverpool - PSV was played in the context of:

Club competition -> International club competiton -> Champions League -> Champions League 2006/2007 season -> 1st group phase

instead of

Croatia - Norway in the context of:

International team competition -> Friendly


I read recursive relationships are the way to go, because you have unknown number of levels!

NOW COMES THE PROBLEM!

The problem is calculating standings and tables and implementing the fact that certain positions in tables at certain points lead to clubs changing competition (for example relegation to 2nd league, promotion, 3rd in group phase of CL goes to UEFA cup etc.)

The fact is that standings are calculated fields and as such you shouldn't put them in tables, you should be able to calculate them in any moment as well as on any historic date!

But the problem is that THERE IS DIFFERENCE IN PLAYING AND SCORING GROUPS (rare but happens)!

Take baseball and NBA for example:
A team can play anyone in MLB and each win is scored the same but only within American or National league and only within AL East, West or Central and only the first spot leads ot play-offs, regardless if 4 best teams all came from AL East!
In contrast NBA teams are scored with Western or Eastern conferenc and best 8 from each progress to playoffs regardless of how many come from sub-divisions!

And finally you have most competitions like leagues and group phases where a team in particular league can only play teams that are exclusive for his group! For example team in Group A of champions league cannot play team in Group B!

My question is HOW DO YOU MODEL THIS and should you try to foresee every possibility in table design OR SHOULD YOU SIMPLY PUT THE RULES FOR STANDINGS CALCULATIONS IN YOUR CODE?

Thanks, I think it's an interesting topic!

No takers on this one?

Or is it badly written?

I'll take a wild stab at both.
Your tables should be designed to stop invalid entries.
And it is always good to validate in the code on top as well.
Better safe than sorry!

no differently than modelling any other application -- entities, attributes, and relationships

in an ideal world I think you should store all this meta data within the db. however it could get quite complex. I have seen applicatrions which had a effectively a rules tables which governed how a particular invoice was calculated (what type of items achieved what type of discount, or what type of order threshold, or what type of end of year rebate was permitted). it could be fiendishly complex to design, but once defined applying the sort of logic you want to apply to your sports / teams / leagues should be relatively easy to use. However it may not be appropriate when you are just starting out on the db design world

if you put it entirely into application code you are going to cause problems if / as / or when the format changes. as you have no direct control over the way teams are promoted or what competitions they compete in the last thing id want to do in your place is to have to write new application code to handle it. Admittedly the OOP (object orientated paradigam) should be a way round this problem.