Database best practice (+ PHP)

[Kemik]

Hi guys,

Assume you have one table for Teams and one table for Players.

If you wish to assign one player per team as the team admin (e.g. to sign up for events, etc) would you add a field to the Team table (e.g. AdminID being the player's ID) or would you add a field to the player ID (AdminFor being the team ID)?

I would like to future proof it so that a player could be admin for multiple teams. This would therefore suggest going for option 1, but I would need to query the Team table every time I display the player's interface to check if they are an admin of any of the teams in the database. Would this still work if you wanted to allow multiple players to be an admin for a team?

Any suggestions for best practice?

Thanks!

 

[Quartz]

Wouldn't a whole new table be better? Third Normal Form & all that?

 

[planty]

As above, a new table called TeamAdmin which would contain TeamID and AdminID to link the two together.

 

[Randomface74]

Teams
ID    Name
1     My Team

Players
ID    Name
2     Steve

TeamMembers
ID     TeamId     PlayerId     IsAdmin
x      1            2           True

SELECT Teams.Name
FROM Teams, TeamMembers
WHERE Teams.Id = TeamMembers.TeamId
AND TeamMembers.PlayerId = 2

 

[soulhunter123]

The "IsAdmin" column, above, would still contain duplicate values (multiple "True" or "False") so it's still not normalized.

You want a third table, "Admins" containing columns "Team ID", "Player ID". Members of that third table are, implicitly, admins, and the structure is normalized.

 

[Ergates]

The "IsAdmin" column, above, would still contain duplicate values (multiple "True" or "False") so it's still not normalized.

You want a third table, "Admins" containing columns "Team ID", "Player ID". Members of that third table are, implicitly, admins, and the structure is normalized.

Given the specified use-case, that would be overly complicated and unnecessary.

 

[chroniclard]

Could you not have a roles table

 

[soulhunter123]

Given the specified use-case, that would be overly complicated and unnecessary.

It's hardly complicated; one extra table? Plus, start as you mean to go on. No excuse for bad habits.

Could you not have a roles table

Yes, however you'd then need a permissions table to differentiate what each role can actually do.

 

[touch]

Given the specified use-case, that would be overly complicated and unnecessary.

soulhunter's solution is the only one that will work with the OP's use-case because of this part:

I would like to future proof it so that a player could be admin for multiple teams.

If the player is only a player on one team but an admin for multiple teams, you need a separate table to store that data.

 

[Ergates]

It's hardly complicated; one extra table? Plus, start as you mean to go on. No excuse for bad habits.

Not "complicated" in the absolute sense - but still more complicated than not doing it. Adding a whole new table to avoid a single binary indicator column is excessive and unnecessary. Not only are you increasing the number of objects in the database, you're adding complexity to the processes that maintain the data (e.g. now if you want to remove a player you need to update 2 bridge tables instead of 1).

A database exists to server a purpose, not to act as a shrine to the 3NF gods. Each situation should be analysed and judged on it's own merits, not on the basis of what some teacher says in a classroom.

 

[Ergates]

If the player is only a player on one team but an admin for multiple teams, you need a separate table to store that data.

You still don't need a separate table - the bridge table can define the nature of the relationship between player and team.