Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Am I takin this Normalization too far?

    Does this show "poor" design? It has been suggested to me to do a "Logical Model" of my data base and that will make it easier to "normalize" the tables. I tried this and come up with the following but I don't know if I am stretching it too thin. One rule of the 2NF is to ensure all tables have a primary key, and as you can see, my tbProjectTeam has a primary key, but that is made up of the entire row. Same goes for the tbDepartmentActivities.



    tbEstimatedProjects
    Reference (PK) | Name | City | Postal |...
    -----------------------------------------------------------
    1 | Some Project | Niagra Falls | N8E7J5 | ....

    tbAwardedProjects
    Project (PK) | Reference
    -------------------------
    1001 | 1

    tbProjectTeam
    Project (PK)| Login (PK) | Activity (PK)
    -----------------------------------------
    1001 | jsmith | Detailer

    tbEmployees
    Login (PK) | First | Last |......
    ----------------------------------
    jsmith | Jim | Smith |.....

    tbDepartmentListing
    Login | DeptCode
    ---------------------
    jsmith | ENG

    tbDepartments
    Code | Department
    ------------------------
    ENG | Engineering

    tblDepartmentActivities
    Code (PK) | Activity (PK)
    ----------------------
    ENG | Engineering
    ENG | Detailer




    Am I taking this too far or is the above structure something to be expected by a "good" normalized table structure?

    Mike B

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is nothing inherentyly wrong with an entire row being a primary key, especially if the table contains only two or three columns. I do suspect that your design needs some tweaking.

    It looks like you are dealing with the following entities: Projects, Employees, Departments, DepartmentActivities. Other tables should establish relationships between these primary entities, such as ProjectMembers.

    Do ProjectTeams exist as persistent entities to which projects are assigned, or do they merely represent the individual employees assigned to work on a given project. If they exist as teams, then you will need an entity table for them as well.

    Can an employee work on more than one team?

    Can more than one team work on a project?

    These questions determine whether you need additional tables for establishing many-to-many relationships.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    [SIZE=1]Originally posted by blindman
    It looks like you are dealing with the following entities: Projects, Employees, Departments, DepartmentActivities. Other tables should establish relationships between these primary entities, such as ProjectMembers.
    ProjectMembers would be the ProjectTeams table.

    Do ProjectTeams exist as persistent entities to which projects are assigned, or do they merely represent the individual employees assigned to work on a given project. If they exist as teams, then you will need an entity table for them as well.
    Represents the individual employees assigned to an activity on a given project within their respected department.
    Example:
    Code:
    Project  | Login      | Activity
    -----------------------------
    1001     | jsmith     | Engineer
    1001     | jsmith     | Detailer
    1001     | mblack   | Manager
    .....
    .....
    1926     | jsmith     | Manager // Must have been promoted!
    There are no set teams, so a team entity would not exist. Any thoughts?

    Mike B

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then what is tbAwardedProjects for? Does it just indicate whether a project's status has changed from "Estimated" to "Awarded"? If so, this should be a field in the project table, and not a separate table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by blindman
    Then what is tbAwardedProjects for? Does it just indicate whether a project's status has changed from "Estimated" to "Awarded"? If so, this should be a field in the project table, and not a separate table.
    Point well taken. This I did because the "project number (eg.1001)" is only assigned if the project is awarded. This project number must be unique. Since it must be unique and a unique value cannot be null, awarded projects need to be their own entity. This system is 2 parts (Cost Control / Cost Estimate). The cost estimate uses the reference as a key and the cost control uses the project number as a key. Make sence?

    Mike B

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd recommend creating an internal ID that is assigned to every project (either Identity or GUID) and then make your ProjectNumber a separate field. ProjectNumber would be defined as Unique and indexed, but would not be the primary key and could thus allow NULL values. Then you can dispense with the "Awarded" column, because the mere presence of a ProjectNumber value would indicate that the project has been awarded.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by blindman
    I'd recommend creating an internal ID that is assigned to every project (either Identity or GUID) and then make your ProjectNumber a separate field. ProjectNumber would be defined as Unique and indexed, but would not be the primary key and could thus allow NULL values. Then you can dispense with the "Awarded" column, because the mere presence of a ProjectNumber value would indicate that the project has been awarded.
    Hmmm, that worked, thank you. I tried that in the SQL Server Diagram Editor and it wouldn't save the table as it at first, but then I tried a fresh database and it worked! Thanks....

    Mike B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •