1

I am currently working on an application where I have 2 Tables. User and Team. User has UserId as PK and UserPassword.

Team table has TeamId as PK and TeamName.

Originally I had one single table where I had UserId, UserPassword and TeamId but it was pointed out to me that the current design is not normalized. Hence I had to break the table into User, Team and also a Join Table named User_Team which contains UserId as PK from User table and TeamId as PK from Team table.

Constraints One User can be in only 1 team and 1 team can have many users.

So as per my understanding, it is a OneToMany from Team to User side.

I have made UserId and TeamId together as a composite key and also made it unique in the JoinTable(User_Team) so that 1 user cannot be in multiple teams.

My query is that is it necessary to break the table into Team table seperately. Cant I just have all 3 fields in one single table. Can someone explain me how it is not normalized. Also let me know if there is a need of JoinTable in this case.

Kshitiz Bathwal
  • 87
  • 1
  • 10
  • 1
    If UserId is required to be unique then it is a key and (UserId,TeamId) cannot also be a key because it is not minimal (irreducible). TeamId→TeamName would be a non-key dependency resulting in an update anomaly. That's why having all three attributes in the same table would be a bad idea. – nvogel Mar 08 '19 at 11:52
  • Could you be more informative by what you meant by minimal(irreducible) and also give me a scenario where update anomaly will arise. – Kshitiz Bathwal Mar 08 '19 at 12:23
  • [candidate key](https://stackoverflow.com/tags/candidate-key/info) If you have the team name in a table where TeamId and TeamName are not keys then that is redundant information that has to be maintained on multiple rows. This is a data quality issue because the dependency between TeamId and TeamName is not enforced. There is the potential that the same TeamId could have multiple different names. – nvogel Mar 08 '19 at 12:37

1 Answers1

2

No, you do not need a JoinTable for a OneToMany relation. Just have a teamId field on your User table and you're good to go.

As a rule of thumb:

  • OneToOne: Have a single table
  • OneToMany: Two tables, the "many" side (User for your case) has a foreign key
  • ManyToMany: Have a JoinTable
GBrandt
  • 685
  • 4
  • 11