I am developing an J2EE app currently. Now I reach the Data Model stage.

I face a problem when I break the table from 2NF to 3NF. Below is example of the difficulty I face, hope can get advice from you all.

1. User_Table
userId (PK)
userName

2. Role_Table
roleId (PK)
userId (FK)
roleDesc

3. Team_Table
teamId (PK)
userId (FK)
teamDesc

I want to retrieve all the role and table for a user. Below is my SQL statement:

SELECT u.userId, u.userName, r.roleDesc, t.teamDesc FROM User_Table u, Role_Table r, Team_Table t WHERE
u.userId=r.userId AND u.userId=t.userId

This SQL will return repeating data to me !!

But if I combine User_Table, Role_Table and Role_Table into a table, using userId+roleId+teamId as PK, this problem will solve. But as far as I know, this 2NF table design will create redundant data.

What is your advice on this problem?

Thank You
Cheers
Ray

From what you provide, it seems that there is no link between roles and teams. So the output you are getting from
your SQL Statement is correct. If I were you, I would break this up into the following tables

Users_Table : UserID(PK), Username
Teams_Table : TeamID(PK), TeamDescription
Roles_Table : RoleID(PK), RoleDescription
Team_Users : TeamID(PK,FK), UserID(PK,FK), RoleID(PK,FK)

So when u wanna find all the roles that a User in the teams he or she is in then u can use the following SQL Statement

SELECT t.TeamID, t.TeamDescription, r.RoleID, r.RoleDescription FROM Teams_Table t, Roles_Table r, Team_Users tu WHERE tu.UserID = :someUserID AND t.TeamID = tu.TeamID AND r.RoleID = tu.RoleID;

Hope that helps

Oh yah, haha.. I forgot, think you wanted the userid and username with the team description and role description.. then it would be

SELECT u.UserID, u.Username, t.TeamDescription, r.RoleDescription FROM Users_Table u, Roles_Table r, Teams_Table t, Team_Users tu WHERE u.UserID = :someUserID AND tu.UserID = u.UserID AND t.TeamID = tu.TeamID AND r.RoleID = tu.RoleID;

Sorry :p