Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Sports statistics database question No.3!

    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!

  2. #2
    Join Date
    Apr 2007
    Posts
    108
    No takers on this one?

    Or is it badly written?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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!
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Riorin
    My question is HOW DO YOU MODEL THIS
    no differently than modelling any other application -- entities, attributes, and relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by Riorin
    .....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?....
    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.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •