[rpgmaker]

Hello i was thinking about making a php team script were users can make a team and other users can join the team.Be for they join the team they have to put in a application then if the users owner of the team accepts then the user would be added to there team. I have been thinking for days how i would lay it out in side the database.

i thought of posting here for idea's.
I guess id have to make it so when the user makes a team it also makes a id for the team. Then i would have a application table with the id of the team they want to join and there username then if the owner of the team accepts it would remove the row from the table and add it to the team table ??

The part im real stuck on is matching the users up with what team there in ( database wise )

This post has been edited by rpgmaker: 16 March 2012 - 01:46 AM

[Dormilich]

the key phrase is "foreign key". essentially, you need a table for the user details, a separate table for the team details and a table to link teams and users.

for example

+-------+---------+-----+----------------+
| users | type    | KEY |                |
+-------+---------+-----+----------------+
| id    | INT     | PRI | auto_increment |
| name  | VARCHAR |     |                |
+-------+---------+-----+----------------+

+-------+---------+-----+----------------+
| teams | type    | KEY |                |
+-------+---------+-----+----------------+
| id    | INT     | PRI | auto_increment |
| name  | VARCHAR | UNI |                |
| owner | INT     | FK  |                |
+-------+---------+-----+----------------+

+---------+------+-----+
| members | type | KEY |
+---------+------+-----+
| user    | INT  | FK  |
| team    | INT  | FK  |
+---------+------+-----+

so you can register users at any time and the team owners can add users by adding them in the linking table (or delete them from there)

to get all members of a team, use a JOIN

SELECT 
    users.name 
FROM members 
JOIN users 
    ON users.id = members.user 
JOIN teams
    ON teams.id = members.team
WHERE
    teams.id = ?

This post has been edited by Dormilich: 16 March 2012 - 02:32 AM

[rpgmaker]

that would work great but 1 problem when a user puts in a application to join the team were would that be stored ? Maybe have a column in the members table called approved ? Then if the result = 1 there there a member and if its 0 they are not ? If its 0 there still waiting to be accepted ? Then all id have to do is select all the ones with a 1 in that row to show there team is that a good idea ? Then when i want to show the application all i do is search in side members approved = 0 for there team ??Then of course if they say no i don't want them in my team it will remove the row and if they want them int here team it will just update it to 1 from 0 ?

Great idea i think ?

[Dormilich]

rpgmaker, on 16 March 2012 - 11:12 AM, said:

that would work great but 1 problem when a user puts in a application to join the team were would that be stored ?

make an application table. in principle* exactly like the members table, only that the meaning is different.

rpgmaker, on 16 March 2012 - 11:12 AM, said:

Maybe have a column in the members table called approved ? Then if the result = 1 there there a member and if its 0 they are not ? If its 0 there still waiting to be accepted ? Then all id have to do is select all the ones with a 1 in that row to show there team is that a good idea ?

IMO, no. whether or not you apply for a team (or several teams) should not be part of either the members table (its purpose is to link teams with accepted members) or the users table (how do you store applications for more than one team?)

rpgmaker, on 16 March 2012 - 11:12 AM, said:

Then when i want to show the application all i do is search in side members approved = 0 for there team ??Then of course if they say no i don't want them in my team it will remove the row and if they want them int here team it will just update it to 1 from 0 ?

if you use a separate table (see above) all you have to do is search the applications table and remove the team-user entry and add it to the members table in case you accept the user.



* - the constraints for the two tables might differ. can you apply for more than one team? can you be part of more than one team? these constraints can be set by unique (composite) keys.

[rpgmaker]

Im gonna code it so users can only join one team at a time.
And the 1 - 0 trick would do the job and be a lot easier than joining tables / results ?

[Dormilich]

a unique key constraint on the members.user column does this even better. and a composite unique key constraint on user-team prevents getting assigned twice to a single team.

table joining is only for output. the constraints (like only one team per user) are set to the appropriate tables.