A Class modelling /design query

Question

How would I model such a situation? How should the database be designed? What classes should I have?

Problem Statement: Each employee belongs at least to one project, each project has many tasks, each task is assigned to at least one employee.

I should be able to churn out

• the employees working on a project.
• the tasks that belong to a project
• the tasks that a given employee is working on.

etc...

Are circular/cyclic relationships a bad design can it be eliminated?

How should the entities represented in a Database? How should the entities be represented using classes?

Thanks in advance,

Answer 1

start with the database and work from there. I'd need more info to recommend a class structure. would you want/need objects for employees? or would they be a property of a project? etc..

DB design:

Projects
    ProjectID
    ProjectName...
    EmployeeID

Tasks
    TaskID
    ProjectID
    TaskName...
    EmployeeID

Employees
    EmployeeID
    EmployeeName...

Answer 2

You did not mention anything about performance or use requirements, so I'm going to answer in a generic way and I'll update my answer if you need more specific information. For the DB tables, I suggest a common normalized approach along these lines.

tblProject
    ProjectID
    ProjectDescription etc.

tblTask
    TaskID
    TaskDescription etc.

tblEmployee
    EmployeeID
    Name etc.

tblProjectTasks
    ProjectTasksID
    ProjectID
    TaskID

tblTaskAssignments
    TaskAssignmentsID
    TaskID
    EmployeeID

Another valid approach would be to create a table defining a project and a different table to define a list of projects. The same would be true for the tasks and the employees. In a real-world application, it would be common for these entities to be well defined in more generic tables, just as you may design a class that contains other well defined objects. For example, you did not mention project resources other than an employee. Those resources could be represented in a schema that defines the type of resource, resource properties, etc. and then joins the resource to a project and/or task.

You could also create a table representing Project Employees, but the data in it would be redundant since you could find the employees assigned to a project by joining the other tables. IMHO, this kind of duplication would only be warranted if the tables are huge and this particular type of query is used very frequently. But I'd still consider other approaches first.

You also asked about the classes. It's hard to be too specific without a better understanding of your goals. In a typical OO design, the classes should represent the Project, Task, and Employee distinctly. But you'll need to tailor that to fit your specific needs.

Answer 3

I would do something like this for the Database Design:

    Create Table Projects
(
 ProjectID int Identity(1,1),
 ProjectName varchar(50) Primary Key NonClustered,
 OtherStuff varchar(255)
)
CREATE CLUSTERED INDEX IX_PROJECTS_ID ON dbo.Projects(ProjectID)

Create Table Employees
(
 EmployeeID int Identity(1,1),
 EmployeeName varchar(50) Primary Key NonClustered,
)
CREATE CLUSTERED INDEX IX_EMPLOYEES_ID ON dbo.Employees(EmployeeID)

Create Table ProjectEmployees
(
 ProjectID int,
 EmployeeID int,
 Constraint pk_ProjectEmpoyees Primary Key (ProjectID, EmployeeID)
)

Create Table Tasks
(
 TaskID int Identity(1,1),
 TaskName varchar(50) Primary Key NonClustered,
 AssignedEmployeeID int, --NOTE: assumes only 1 employee per task
 OtherStuff varchar(255)
)
CREATE CLUSTERED INDEX IX_TASKS_ID ON dbo.Tasks(TaskID)

Create Table TaskPrecedents
(
 TaskID int,
 PrecedentTaskID int,
 PrecedentType Char(2) --Codes, you'll have to work these out
 Constraint pk_TaskPrecedents Primary Key (TaskID, PrecedentTaskID)
)

Answer 4

I'll try to answer your questions in as generic a way as I can, and avoid repeating the specific table structures as in the previous responses. Generally speaking, cyclic relationships between your entities are not a bad thing...on the contrary, they are quite common:

There are many Projects
Projects have Employees
Projects have Tasks
Employees are assigned some Tasks

While a Project has Employees...and Employee also has a Project (or, possibly many Projects if an employee can work on more than one project at a time). From a database perspective, when you create a foreign key, that "circular" relationship exists whether you want it to or not.

The more important question is, from a conceptual perspective, does it matter if an Employee knows what project(s) it is a part of? While it is probably very important that a Project know what employees are working on it...it may not be important that an Employee know the project its working in. This is what is called "Navigability", and unlike our database structure, we CAN control this with our classes. A Project object would have a collection of Employee objects, but the Employee object does not necessarily need to have a Project property (or collection of Projects.)

There is no canned answer that I can give you regarding navigability. Thats usually subjective, and depends on the needs of your business. If the business that your modeling has the concept of an employee knowing which projects they are working on, and that knowledge is important to completing the processes your business logic will perform...then you need that circular relationship. Same goes for navigability between employees and tasks, projects and tasks, etc.