Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: How to manage extremely complex data structures?

    It's a sports sport -> subsport -> federation -> country -> league -> club structure for a sporting site.
    It's very complex because each sport has different structure (while there around thousand different sports). And I gotta make a CMS for it. My CMS consists of various forms for inserting various types of data, each data type has some info, others not.
    (For example, countries can't have email-s and they're being selected from the coutries table. Or sports have subsports, those subsports have parent's, and federations under them contain e-mail insert in the form.)

    There's about 10-20 thousand sport clubs, hundreds of leagues, bunch of subsports.
    And I think I have it all done in a month (data structure system and CMS), but as we started to insert data, we started to see many exceptions for the system. I.E. Basketball has around 3 layers of leagues, so I gotta rebuild the system to have 1 or more layers of leagues.
    It feels like never ending. I constantly have to change it in the same ways, and I don't feel I'm doing it the right way.
    Is there a more flexible way to do this?

    Examples of sport structures:

    Soccer -> No subsports (skip) -> Main federation -> SubFederation -> Countries -> Leagues ->
    Aikido -> Countries -> Clubs
    Basketball -> Federation -> Area (U.S.) -> League -> Subleague -> Subleague level 2-> Clubs
    Clubs
    Adventure/extreme category -> Sport -> League (has the same name as the country) -> Club
    Oh, btw I'm using PHP/AJAX and MySQL.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you make your league structure recursive that should get rid most of the problems

    eg
    table League
    ID
    Description
    ParentLeagueID 'fk that references ID in league table

    organising structure likewise

    eg
    Football
    Code:
    FIFA
    |--AFC
    |--CAF
    |--CONCACAF
    |--CONMEBOL
    |--UEFA
        |--ENGLAND
        |--GERMANY
        |--NORTHERN IRELAND
        |--SCOTLAND
        |--WALES
    
    ID    ORGANISATION              PARENT ORGANISATION
    10    FIFA
    21    CONCACAF                  10
    937   CANADA                    21
    36    UEFA                      10
    44    The Football Association  36
    ..of course you could be 'cute' and use the country ISO prefix as the ID....

    whether you need a separate organisation structutre AND league configuration is I suppose moot

    eg
    Code:
    ID    ORGANISATION              PARENT ORGANISATION
    44    The Football Association   36  
    666   The English Premier League 44
    45    Championship               44
    46    League 1                   45
    47    League 2                   47
    32    conference national        44
    33    conference north           32
    34    conference SOUTH           32
    1     Northern Premier League    32
    99    Southern Premier LEAGUE    32
    you coudl then flesh out the next layer within the same table
    eg

    Code:
    ID    ORGANISATION              PARENT ORGANISATION
    44    The Football Association   36  
    666   The English Premier League 44
    6161  Manchester City             666
    1616   Trafford Town               6161
    2     Bolton Wanderers            666
    howeever you could come unstuck if your model supports organisations with multiple sports. ferisntance some clubs may have a football and rugby team so you would perhaps have to abstract the organsiation by usiung a foreing key to a club and identify what type of sport the organsiation signed up for


    eg
    sport types
    1 Football
    2 Rugby League
    3 Rugby Union
    4 Cricket

    your organsiation table
    SPORTTYPE
    ID
    ORGANISATION
    PARENT ORGANISATION

    eg
    1 10 FIFA
    1 36 UEFA 10
    1 44 The Football Association 36
    2 132 ICC
    2 444 ENGLAND & WALES
    2 555 DIVISION 1 444
    2 557 LANCASHIRE 555
    2 556 DIVISION 2 555
    2 32 AUSTRALIA
    ..
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2011
    Posts
    4
    Hi again! Wow it's really nice to see someone make an effort like that. So yea, you got it all right! And it's pretty much the way I did it in the DB. My CMS is the thing we gotta problem with. I constantly gotta keep building new blocks of code for exceptional structures. Just wanted to find a more flexible way to design it.
    (But only, to preserve it's type, i put TYPE ENUM ('Sport','Federation','League','Club'')).

    Thx for the one club - multiple sports idea.

Tags for this Thread

Posting Permissions

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