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 BThere 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.|||[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:
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|||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.|||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|||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.|||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
