0

UPDATE: Company can have multiple Projects and Company also have Employees. An employee can only have one Company and a Project can also have only one company. A project has several tasks. Out of these tasks, an Employee will only be assigned to some of the tasks.

The employee can only be assigned tasks for project that he/she is assigned to.

Please review the following and help on how I should create the database design and the final entity model UPDATED DIAGRAM based on the two comments: If this ER diagram looks good, could you please provide guidance on how the EF 4.1 entity classes should be created, especially for EmployeeProjectTasks table. I am new to EF 4.1 Code First and would like some direction with creating these classes

If this ER diagram looks incorrect, please recommend revisions.

enter image description here

Pratik Kothari
  • 2,446
  • 2
  • 32
  • 54
  • For you, can a task exist without being assigned to a project? – Mike Sherrill 'Cat Recall' Jul 25 '12 at 19:34
  • @catcall No, a task is always associated with a project. In fact, should EmployeeProjectTasks have 4 columns (UserID, ProjectID from EmployeeProjects and TaskID, ProjectID from Tasks table)? – Pratik Kothari Jul 25 '12 at 19:38
  • 2
    It doesn't need duplicated columns. It needs multiple, overlapping foreign key constraints: `foreign key (UserID, ProjectID) references EmployeeProjects (UserID, ProjectID)` and `foreign key (TaskID, ProjectID) references Tasks (TaskID, ProjectID)`. – Mike Sherrill 'Cat Recall' Jul 25 '12 at 20:47
  • @Catcall - please review the updated diagram. I think I have it now. Thanks for all your help. How should I create the Code First classes? – Pratik Kothari Jul 26 '12 at 15:55
  • 1
    That should probably be a different question. Or try Google. I found this: [Generating EF Code First Model Classes from an Existing Database](http://weblogs.asp.net/jgalloway/archive/2011/02/24/generating-ef-code-first-model-classes-from-an-existing-database.aspx). – Mike Sherrill 'Cat Recall' Jul 26 '12 at 16:34

2 Answers2

0
  • currently you don't have the information which employee is assigned to which project
  • the relations look like employee / task is an m:n relationship, i think you didn't do that on purpose
  • is employeetasks inherited from task or is it the relationship entity?
Verena Haunschmid
  • 1,252
  • 15
  • 40
  • Hi ExpectoPatronum, EmployeeTasks is the relationship entity and is the mapping table between the Tasks and Employees. Every Task is associated with a Project, so based on the mappings defined in the EmployeeTasks, I should be able to know the Projects mapped for this employee, correct? One other thing I could do is define a unique constraint on TaskID and ProjectID columns inside Tasks table and add ProjectID column to the EmployeeTasks....that will allow me to quickly query employee tasks or employee projects.... – Pratik Kothari Jul 25 '12 at 18:44
  • @PratikKothari: You said, "Every Task is associated with a Project, so based on the mappings defined in the EmployeeTasks, I should be able to know the Projects mapped for this employee, correct?" That's the wrong question. The right question is, "Can I store the fact that employee 12 is assigned to project 106, even if there are no tasks yet assigned to that employee?" And the answer to that is, "No, you can't." But that's a requirement; you need a table to implement that requirement. – Mike Sherrill 'Cat Recall' Jul 25 '12 at 19:15
  • @Catcall thanks for your comment and I agree with it. I have revised the diagram. Could you please review and suggest? Thanks – Pratik Kothari Jul 25 '12 at 19:19
0

The employee can only be assigned tasks for project that he/she is assigned to.

Your ER model doesn't enforce that.

That's because of the non-identifying relationship between Project and Task, so the propagation of ProjectID is cut-off in that branch of the diamond-shaped dependency and EmployeeProjectTask.ProjectID only references EmployeeProject (but not Task).

The model that ensures EmployeeProjectTask is connected with EmployeeProject and Task from the same Project would look like this:

enter image description here

Note how Task has a composite PK {ProjectID, TaskNo}, and how Project.ProjectID is propagated down both "branches", and merged at the bottom of the "diamond" (resulting in the EmployeeProjectTask.ProjectID, with FK to both EmployeeProject and Task).

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • could you please post the image again. I can't see it. Thanks for your help. – Pratik Kothari Jul 26 '12 at 10:54
  • @PratikKothari Looks fine to me. This was probably a temporary problem wit the StackOverflow.com itself. Can you see it now? – Branko Dimitrijevic Jul 26 '12 at 12:42
  • Branko, thanks. I can see the image now and understand. Just thinking, what if there was one more entity - Company. Company can have multiple Projects and Company also have Employees. An employee can only have one Company and a Project can also have only one company. Do I then propagate CompanyID from Project and Employee in the same way to EmployeeProjects table? Also, will the CompanyID propagate to EmployeeProjectTask? – Pratik Kothari Jul 26 '12 at 14:52
  • updated my diagram to reflect your comment. How should I create EF 4.1 code first classes around this design? – Pratik Kothari Jul 26 '12 at 15:55