MySQL Table Question > Array?

[Sixii]

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?

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?

Tia.

[Peter Vernout]

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

> I'm programming a Gaming Ladder/Tournament system.
> Let's say I have the following tables:
>

<CUT>

> 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?

>
> 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 suggest you create an extra table linking teams on ladders, instead of
using a ladder field on the team table.
It can be a simple table with only 2 key fields (ladder and team) or can
even hold more information for that team in that particular ladder.

Cheers,
Peter

[renster]

"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.'