Third Normal Form (3NF) Database Problem

Discussions

General J2EE: Third Normal Form (3NF) Database Problem

  1. Third Normal Form (3NF) Database Problem (2 messages)

    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
  2. 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

  3. 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