MySQL Table Question > Array?

"Sixii" <sixii@yahoo.com> wrote in
news:yWVfb.84383$lh.17543286@amsnews02.chello.com:

> I'm programming a Gaming Ladder/Tournament system.
> Let's say I have the following tables:
>
> PLAYERS
> -------
> * Player_ID
> * Player_name
> ...
> * Team_ID
>
> TEAMS
> -----
> * Team_ID
> * Team_Name
> ...
> * Ladder_ID <--- Notice this one!
>
> LADDERS
> -------
> * Ladder_ID
> * Ladder_Name
> * Ladder_Description
> ...
>
> The question is about the 'Ladder_ID' field in the TEAMS table.
>
> For instance, when a team joins 2 ladders/competitions, how do I store
> this?

A table with two fields. I've shown a row_id but you don't really need it
(but good to have), unless you wanted to maybe add an extra field to
track scores or make comments for a particular team in a particular
ladder. I've also made it so that teams are constructed the same way -
ie. in case you ever need to have one player in more than one team.

>
> Do I fill the LADDER_ID field in the TEAMS table with an ARRAY of
> values (e.g. Ladder ID's 1, 3 & 4) ? Is there a better way to
> associate teams with multiple Ladders?

I think this is a bad idea. With the competition table below you can
select the Ladder_ID you want and return all the Team_ID's in that
competition. the TEAM_MEMBERS and LADDER_MEMBERS table are essentially
composed of foreign keys with multiple team_id and ladder_id entries for
each team or ladder

I'd have:

PLAYERS
-------
* Player_ID <- Primary Key
* Player_name

TEAMS
-----
* Team_ID <- Primary Key
* Team_Name

LADDERS
-------
* Ladder_ID <- Primary Key
* Ladder_Name
* Ladder_Description

TEAM_MEMBERS
-------
*Row_ID <- Primary Key
*Team_ID
*Player_ID

LADDER_MEMBERS
-------
*Row_ID <- Primary Key
*Ladder_ID
*Team_ID
*Comment (optional)

- e.g.

LADDER_MEMBERS
-------
0 1 1
1 1 2
2 1 3 'They did ok this comp, might do better next time'
3 2 3 'This team really did well this time around'
4 2 4
5 3 1
6 3 5
7 3 10
8 3 2 'This team really sucked at this comp.'