Page 1 of 1

Junction tables - Help simplify

Posted: Fri Jan 11, 2013 1:31 pm
by dharmeshb
May I get some help to simplify my database design? I am doing my first project a sports league website with the goal to learn PHP and MySQL.
To keep things simple I am using role based security.

Here are my tables
Users - id, Username, Password, FirstName, LastName, Email
Roles - id, Role
Users_Roles - id, UserId, RoleId

Leagues - id, Name, Alias
LeagueTypes - id, LeagueType
Seasons - id, Year
LeagueDetails - id, LeagueId, TypeId, SeasonId

Teams - id, Name, Alias

I am planning to use LeagueDetails as my table to link other relations.. eg. Teams under a league, League admins, etc.

For this I will need to make junction tables
Leagues_Teams - id, LeagueDetailId, TeamId
Leagues_Admins - id, LeagueDetailId, User_RolesId
Teams_Admins - id, TeamId, User_RolesId

Is there a simpler way of doing this? Am I making database model very complicated than it needs to be? Please advise

Re: Junction tables - Help simplify

Posted: Fri Jan 11, 2013 2:42 pm
by requinix
You only need a junction table if there is a many-many relationship between the entities. That is, you only need the table between X and Y if one X can be related to many Ys and one Y can be related to many Xs. If it's a one-many or many-one then you can put X's ID directly in the Y table (or vice versa).
As long as you're following those rules then however complicated the design is, it will be the minimum degree of complexity necessary.

Re: Junction tables - Help simplify

Posted: Sun Jan 13, 2013 12:27 pm
by dharmeshb
Thanks requinix. After your comment it made me think I will need a junction table for Leagues_Admins but not for Teams_Admins since there will only be one team admin for one team.