Red and blue team (mysql)

[eleven0]

I have one table where i store all of my members information. I'm trying to divide them into teams, and show the team members on a web page.

 

maybe have different tables for each team?

[djpic]

Does each team have a description or more options?  If so, I would make two tables.  One with the members but in the members table, add a column named "teamID" then create another table named team.  In this table include TeamID (use an auto primary key) and then have a description, title, etc. for the teams.

 

Now you can set options both for the teams and the members.  Just have to connect the teamID in the team table and the teamID in the members table (maybe a good idea to have a memberID as well).

 

If you don't want it to be that complicated, then I would just add a column in your current table with the team name.  Making two separate tables for each team seems to be a waste of a table to me.

[Psycho]

Just create a column for "team" in the member table and put the appropriate value for each member. Lets say you use 'r' and 'b' to identify the team assignment.

 

You can then get all red members using the query

SELECT * FROM members WHERE team='r'

 

Create a separate table is not just a "waste of a table" it is bad practice.

[craygo]

It's not bad practice if the Team has alot of fields to discribe it, or even just a couple. Why type the description of the team a hundred times when you can just have it once in the teams table then link it with a simple number.

 

Team table          Members table

teamid --------|  memberid

teamname        -- teamid

teamcolor            name

 

Now you can link them with a query

$sql = "SELECT t.teamname, t.teamcolor, m.name FROM membertable AS m JOIN teamtable AS t ON m.teamid = t.teamid";

 

Ray

[haku]

Sorry Cray, but mjdamato is correct on this one. They are like information (as in 'alike' not is in 'kind of like') and as a result should be stored in the same table, with an identifier for which team they are on. This is better usage of databases.

 

What if he wants to collect information on all his players at some time? Rankings regardless of teams etc. If its two tables he has to query two tables then use PHP to organize the results, and it turns into a mess of code. With one table it can be organized by the database before it is even served to PHP.

 

I understand your line of thought behind having many columns in the table, but what you have to realize is that databases were made to handle large amounts of information - its what they do.

[craygo]

Well I guess it is all personal preference, because i use multiple tables all the time and have never come across a problem with organizing data from more than one table. If you want just data from one table then don't query the other one. I work with Oracle, MSSql and MySql(for personal use) and every enterprise database i have worked with uses multiple tables to link information.

 

I guess the rest of the world is wrong!

 

Ray

[Psycho]

"maybe have different tables for each team?"

 

I only meant that creating a table for RED "members" vs a table for BLUE "members" would be a poor choice. If the "teams" entities need additional data then having a separate table to describe team specific information would be the logical choice. Cray is correct that you should not repeat team specific data on a member by member basis.

[drewbee]

Craygo is correct on this one. The other methods mentioned above are denormalized format, and can lead to maintance problems, not to mention redundant data. I have never run into problems running queries on normalized data, and infact keep things rather clean. What if he wants to update "red team's" name to "The Jaguars" ? He would have to do a row update for every single person on this team. With a normalized format, only 1 row is updated in the child table. Nuff said.

[revraz]

If the "Team" had more information than just Red or Blue then yes I would make another table.  Because you know down the road, you will want to expand what you can do with that data.

[haku]

I still disagree. If the types of data for the two types of players is the same with the exception of a team name, then they should be stored in the same table. If you start going off on information relating to the teams, THAT Information should be stored in a different table, however, both teams will also be stored in the same table on there.

 

Just because it works doesn't mean its the correct usage of databases. When its something small like a site for video game teams, its not going to be a big issue. But if we are talking pure database theory, then separating the players into two different tables because they are on different teams is not the correct way to use a database. Study some database theory and you will see that I am correct.

[deadonarrival]

Two tables

user

-user_id

-name

~other info

-team_id

 

team

-team_id

-name

~other info

 

Any arguments with that?

 

If you ONLY want each person to be a member of either red or blue teams, just using "r" and "b" in a "team" field is sufficient. If you start to store information ABOUT the team (eg name/leader/motto/logo) you definately need a new table.

 

Personally I have a new table for each major entity, to allow for future expansion. Better to be fully normalised and relational then at a stage where you may aswell use flat-file.

[eleven0]

I still disagree. If the types of data for the two types of players is the same with the exception of a team name, then they should be stored in the same table. If you start going off on information relating to the teams, THAT Information should be stored in a different table, however, both teams will also be stored in the same table on there.

 

Just because it works doesn't mean its the correct usage of databases. When its something small like a site for video game teams, its not going to be a big issue. But if we are talking pure database theory, then separating the players into two different tables because they are on different teams is not the correct way to use a database. Study some database theory and you will see that I am correct.

 

That's what I was going to go ask.

I'll have over 10 teams and over 15 fields. The reason I asked here was that I'll be moving players from one squad to another. I just didn't know how. I don't think I'll be storing information about the teams.

[deadonarrival]

If you want to change their team, just query

UPDATE `user` SET `team`='colour' WHERE `username`='$username'

 

Or if you do use two seperate tables

UPDATE `user` SET `team`='team_id' WHERE `username`='$username'

[haku]

deadonarrival's table structure was correct, as was his last explanation of how to change a player's team. That's good advice.

[eleven0]

I got another question on this one.

 

I have got members who are in both teams. I gave numeric numbers for each squad. For instance, if the field has "10", then the person would be in red team. If the number is 15, person would be in blue team.

 

I'm trying to show all of my teams on the same page and separately on different pages. But when I type "10, 15" in the field. It won't consider that person as part of both teams.

 

What can I do for this?

[haku]

It's not exactly 100% clear what you mean. What do you mean by "type 10, 15 into the field"? Do you mean in your code, or do you have a form into which you are typing this? Also, can you show us your code?

[eleven0]

I got a column name "team". If a person has "10" in that column, he would be in red team. So i can show all member of red team.

 

Like this;

 

SELECT * FROM members WHERE team='10'.

 

How can I put a member in both teams?

[Cagecrawler]

I'd use three tables in that case.  A 'user' table for each player (could easily double up as storage for login info etc), a 'teams' table listing each team and it's info and a 'user_team' (or something to that effect) table which stores each user-team relationship.

 

Altering deadonarrival's table structure, remove team_id from the users table.  Then create a third table ('user_team') with three columns - id, user_id and team_id.  If a player is in more than one team, they have more than one entry in the table.

[eleven0]

Could i use more than one WHERE clause? if so, how? I was thinking having another column.

[Cagecrawler]

If they are limited to two teams, you could have 'team1_id' and 'team2_id'.  Then use SQL something like this:

SELECT * FROM user WHERE team1_id = 10 AND team2_id = 15

 

Of course, only using two tables means adding an extra column if you want to allow them to be in another team, which should be avoided.

[haku]

If you want them to be in two different teams, creating a new table as cage-crawler said is your best way to go. Have two columns - one for player_id and one for team_id. This way you can create multiple rows for each player, one for each team they are on. And you can create multiple rows for each team - one for each player on the team.

 

Now if each player is on only one team, but you want to pull out all the players from team 10 AND team 15, then you would use an OR statement.

 

"SELECT player FROM players_table WHERE team_id=10 OR team_id=15"

 

This will give you all the players on both teams 10 and 15.

 

If you use an AND statement:

 

"SELECT player FROM players_table WHERE team_id=10 AND team_id=15"

 

It will give you only the players who are on both teams, and no players that are on only one of those two teams.

[laffin]

dun keep the team id in the user profile.

keep the team info in a seperate id. it's called a many to 1 relationship.

keep the userid in the team table instead.

 

[pre]

TeamTable      UserTable

id            +-- id

userid  ---+    username

name

[/pre]

 

[deadonarrival]

Laffin, if you do that, you can only have one person in each team... you have your many-1 relationship the wrong way round.

 

You DO want the teamid in the user table, that way each person has 1 team, but each team has lots of people.

 

Your way would involve each person having lots of teams, but each team having 1 person :s

[laffin]

No, if u have teamid in user table

than user can be assigned to 1 team only, unless u add more fields for more teams

 

Let me do this in SQL syntax instead

 

CREATE TABLE users {
id integer NOT NULL AUTOINCREMENT PRIMARY KEY default '0' ,
username varchar(32) NOT NULL default ''
}

 

okay now for the Teams

 

CREATE TABLE Teams {
id integer NOT NULL AUTOINCREMENT PRIMARY KEY default '0' ,
name varchar(32) NOT NULL default ''
}

 

with a third table for the team members and which team they belong to

 

CREATE TABLE TeamMembers {
userid integer NOT NULL default '0',
teamid integer NOT NULL default '0',
PRIMARY KEY ('userid','teamid')
}

 

with both being a key, keeps the db from creating duplicate user/team entries

 

[deadonarrival]

That's a many-many relationship... not a 1-many. And he stated he only wanted each person to be a member of one team. So the original method of teamid in the user field means each one person is a member of one team, each team can have unlimited members... that's all he required.

 

Ie no need for a linking table.