An array of tables?

[rawb]

I just wanted to get some advice from you experts before I embarked on a little project.. I'm trying to make an application that will manage a series of 1v1 tournaments for a video game (any of you ever played TFC?).

 

Anyways, the best way I could think of doing it was creating the following tables.. I'm a little new at this whole relational database thing so don't laugh if this is amateur..

 

users

id, name, etc.

 

tournies

id, date, etc.

 

teams[tourneyID]

userId, seed, etc.

 

matches[tourneyID]

id, winnerId, loserId, winnerScore, loserScore, etc.

 

As you can see, I would need two arrays of tables (one for teams and one for matches).. one table of each for each corresponding tourney.

 

I realize that I could just put another field in each of these tables to correspond to the tourneyID but that just doesn't seem like a great solution to me.  Also, I could just name the tables teams1, teams2, etc. and matches1, matches2, etc. and that would probably work just fine.. but I was wondering if there is a better/more accepted way to do this in mysql?  I hope I explained that so it makes sense.  Any advice before I begin?

[georg]

I think, I's the best solution to add another field (maybe a team_id/match_id), because many tables seem to decrease the speed of mysql.

Just create an index on the id. There you can select the different teams/matches.

[bubblegum.anarchy]

Since a single user references a single team the user table should hold the reference key, in this case a team_id

 

user.id

user.team_id

user.name

 

team.id

team.name

 

The following would then provide a list of all the users and their respective teams:

 

SELECT user.name, AS user_name, ifnull(team.name, 'No Team') AS team_name
FROM user
     LEFT JOIN team ON user.team_id = team.id
ORDER BY team.name, user.name

 

Use a single table for tournies and matches as well... search google for more information on relational database design.