Help with database structure

[ShopMAster]

I'm trying to create a database for a very unique kind of league and I need a little assistance on how I need to create the database structure.

Each league will have teams.  Teams consist of players.  There are 15 stadiums in each league, where a team can win 6 flags at each stadium.  I need to set it up so that when player A plays player B for a flag at Stadium C, they get credit for it.  I think I have the league, team, players, and flags(battlemodes) tables set up, but I have no idea how to set up the stadiums.  Each league may have different flags(battlemodes) at a particular stadium.  Meaning one team could do 4 flags(battlemodes) per stadium, where the other team can do 6 flags per stadium.  Each flag is a different kind of battle and varies depending on the stadium.

Below is how I have the structure for everything besides the stadium.  also if you see a better way to do this, please advise.  I'm in the begining stages so I can go back easily and I'm pretty new at this.

[code]
CREATE TABLE `battlemodes` (
  `bmid` int(11) NOT NULL auto_increment,
  `name` varchar(55) NOT NULL default '',
  `rules` mediumblob NOT NULL,
  `description` mediumblob NOT NULL,
  PRIMARY KEY  (`bmid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `leagues`
--

CREATE TABLE `leagues` (
  `lgid` int(11) NOT NULL auto_increment,
  `name` varchar(55) NOT NULL default '',
  `description` longblob NOT NULL,
  `system` varchar(11) NOT NULL default '',
  `numteams` int(11) NOT NULL default '0',
  `farmsystem` char(3) NOT NULL default 'No',
  `commish` varchar(35) NOT NULL default '',
  `commishemail` varchar(40) NOT NULL default '',
  `cocommish` varchar(35) NOT NULL default '',
  `cocommishemail` varchar(40) NOT NULL default '',
  `forumlink` varchar(60) NOT NULL default '',
  `team1` int(11) default NULL,
  `team2` int(11) default NULL,
  `team3` int(11) default NULL,
  `team4` int(11) default NULL,
  `team5` int(11) default NULL,
  `team6` int(11) default NULL,
  `bm1` int(11) default NULL,
  `bm2` int(11) default NULL,
  `bm3` int(11) default NULL,
  `bm4` int(11) default NULL,
  `bm5` int(11) default NULL,
  `bm6` int(11) default NULL,
  `bm7` int(11) default NULL,
  `bm8` int(11) default NULL,
  `bm9` int(11) default NULL,
  UNIQUE KEY `lgid` (`lgid`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `players`
--

CREATE TABLE `players` (
  `id` int(11) NOT NULL auto_increment,
  `player_name` varchar(20) NOT NULL default '',
  `player_perc` int(11) NOT NULL default '0',
  `player_randomwins` int(11) NOT NULL default '0',
  `player_randomlosses` int(11) NOT NULL default '0',
  `player_flagwins` int(11) NOT NULL default '0',
  `player_flaglosses` int(11) NOT NULL default '0',
  `player_lastw` varchar(25) NOT NULL default 'nobody',
  `player_lastl` varchar(25) NOT NULL default 'nobody',
  `player_bmhs` int(11) NOT NULL default '0',
  `player_bmdiv` int(11) NOT NULL default '0',
  `player_bmrival` int(11) NOT NULL default '0',
  `player_bmcfl` int(11) NOT NULL default '0',
  `player_bmnfl` int(11) NOT NULL default '0',
  `player_bmnk` int(11) NOT NULL default '0',
  `player_bmud` int(11) NOT NULL default '0',
  `player_bmstarless` int(11) NOT NULL default '0',
  `player_team` varchar(20) NOT NULL default '',
  `player_firstname` varchar(55) NOT NULL default '',
  `player_favteam1` varchar(55) default NULL,
  `player_favteam2` varchar(55) default NULL,
  `player_favteam3` varchar(55) default NULL,
  `player_age` int(2) NOT NULL default '0',
  `player_rival1` varchar(55) NOT NULL default '',
  `player_rival2` varchar(55) NOT NULL default '',
  `player_rival3` varchar(55) NOT NULL default '',
  `player_motto` varchar(55) NOT NULL default '',
  `player_avatar` varchar(255) NOT NULL default 'http://www.mwleagues.maddenwars.com/images/noavatar.gif',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `player_name` (`player_name`),
  UNIQUE KEY `uid` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `teams`
--

CREATE TABLE `teams` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `random` int(11) NOT NULL default '0',
  `flags` int(11) NOT NULL default '0',
  `secured` int(11) NOT NULL default '0',
  `penalty` int(11) NOT NULL default '0',
  `misc` int(11) NOT NULL default '0',
  `total` int(11) NOT NULL default '0',
  `leader` varchar(55) default NULL,
  `coleader` varchar(55) default NULL,
  `teamdesc` varchar(255) default NULL,
  `logolink` varchar(255) default NULL,
  `motto` varchar(255) default NULL,
  `banners` varchar(255) default NULL,
  `leagueid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

[/code]

Thanks for any and all help.

Shop

[fenway]

A few initial comments:

rules - I hope that's not going to be parsed
teamN, bmN -- not very normalized
player_* - some of these fields seems like real-time values, not static values

I guess I"m not clear what you're trying to set up from your initial description.

[ShopMAster]

Take a look at http://www.maddenwars2005.com/mwtheleague/index.php  There are sub links to the stadiums, player stats, and etc .... under the header.

This is a league with 6 teams with 10 players each.  I'm trying to find a way to automate all of this so the commissioner doesn't have to insert flags for stadiums via frontpage as he is doing now.

I'm trying to create the right database structure so that all these tables work together.  Right now this is just one league but I want to create other leagues just like this.


I don't know what I should put for rules, but battlemode will have a set of rules.

teamN, bmN; once again I didn't know how to go about linking everything so I thought for the league you have to specify which team (id) and battlemode (id) that particular league was using.

player_* - what do you mean by real-time values.  Is there a better way to do this.

That's my real delima, I know there has to be a better way to make these tables interact with each other through a better structure.  I'm pretty new to this, so any help would be appreciated.

[fenway]

Well, you'll obviously need tables for players, teams, leagues and stadiums.  Apparently, matches have flags/battlemodes... shouldn't be a problem to specify when teams are involved in which.  I don't know what rules are -- unless it's just plaintext, the BLOB threw me off.  Sounds like you just need a table of match-ups.  As for the player stats, it seems like this is a mix of static values (like name) and real-time values (like number of wins).

[ShopMAster]

[quote author=fenway link=topic=123648.msg513268#msg513268 date=1169743630]
Well, you'll obviously need tables for players, teams, leagues and stadiums.  Apparently, matches have flags/battlemodes... shouldn't be a problem to specify when teams are involved in which.  I don't know what rules are -- unless it's just plaintext, the BLOB threw me off.  Sounds like you just need a table of match-ups.  As for the player stats, it seems like this is a mix of static values (like name) and real-time values (like number of wins).


[/quote]

Exactly.  Sorry I threw you off with the blob thing, just didn't know what to put it in.  So i have I think players and team tables set up ok (see sql in first post), but what is a solid way to do the leagues and the stadiums.  Especially the stadiums.    The problem with the flags/battlemodes is that some leagues may only use 5 flags/battlemodes; where another league could use all 8.  So I need a structure that allows the league to specify which battlemodes they use (so I can query it into a select box when people enter in their games) and a way that when a player wins a battlemode game at a stadium, it will put in a flag for that team, for that particular flag, at that particular stadium.

Thanks fenway for continued patience on this board.  :)

[fenway]

Don't do the limits for flags that way -- simply have another table with all of them, and have a league field that specifies the maximum.

[ShopMAster]

another table??, but how do I specify which battlemodes a certain league uses and what about the stadiums.  Arggggh, I think I just need to start all over and rethink this a little.

[fenway]

With yet another table... that lists valid combinations of leagues and/or battlemodes and/or stadiums.

[ShopMAster]

fenway, how would you set it up because I am clueless as to what you mean.  Can you give me the syntax for the stadium table you would set up for this?

[fenway]

Any time that you need to make many-to-many relationships, it's almost always a bad idea to determine ahead of time how "many" there should be -- i.e. by adding N columns to either or both tables.

[ShopMAster]

well, I'm still clueless so I posted in the Freelance forum.  If your looking to make some money check it out.  I need help and I know you are trying to guide me to what needs to be done, but I'm at the point where I just need someone to do it for me.

Thanks for your guidance fenway, sorry if I wasted your time.

[fenway]

Not at all... I'm sorry if I've been posting things that you're unfamiliar with at your current level... it happens sometimes.  There are many good dev articles on the mysql website, but to be sure, none of these will help you get the job done in the very near future if you're having a lot of trouble.