New Database or another Table?

[cags]

Ok, I've mentioned it before so some of you may know I've been messing about creating a site for Pool League administration/stat tracking. You can see attached a simplified table layout. Currently it contains all (well alot of) information relative to a season. I've been thinking about how to introduce archive ability. At the end of the season should I create a new database and use the differen't database based on which stats people are trying to view, or should there be a 'Season ID' attached to each 'Competition'?

 

On a side note, since the site also has news/guestbook and other site related things not directly linked to the league/a season, should these tables be stored in the same database or should I consider having a seperate database for them?

 

[attachment deleted by admin]

[JustLikeIcarus]

Ive actually done this however mine was for several different sports/teams etc...  What ive found as the best way is a seasons table with a flag indicating active/inactive then teams have a season_id.  Then you just have your queries ignore inactive seasons. Make sense?

[cags]

Yer makes sense. It just gets confusing because I'll have multiple seasons, each season there is a league consisting of x divisions, there is a divisional cup, a league cup, a super 4's cup, singles and doubles competitions. Each of those competitions have a different format some are normal 'league tables' some are knockout ladders (thankfully none are like the UEFA cup with group sections then knockout (yet)). The competitions vary between winter and summer season as does the format of the competitions. Basically it's a nightmare with that much interlinking information it's driving me nuts. I have a working system for tracking basics, but trying to expand it is proving more than a headache especiallly trying to keep the tables normalised, there are id's floating around all over the place and I'm seemingly having to do 3-4 joins even to fetch the most basic sets of information. I already 'hacked' together a fake table that updates weekly to produce a league table because of the complexity of generating one on the fly. I want to finish this project, but everytime I sit down and look at it, I think about it for awhile, get more and more confused then depressed, then give up.

[JustLikeIcarus]

Are you using some kind of ORM to ease the headache?  If not I suggest using the Kohana PHP framework www.kohanaphp.com Once you get your models created it makes everything easier. For example if you had Seasons related to Teams and Teams related to Members printing the list of teams would be as simple as (Note: this is a very rough example.)

foreach($season->teams as $team){
  echo $team->name;
  foreach($team->members as $member){
    echo $member->name;
  }
}