Junction tables - Help simplify

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Junction tables - Help simplify

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Junction tables - Help simplify

Post 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.
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Junction tables - Help simplify

Post 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.
Post Reply