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
Junction tables - Help simplify
Moderator: General Moderators
Re: Junction tables - Help simplify
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.
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
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.