当前位置：文江博客话题详情

A类建模/设计查询

发布于 2024-07-22 16:34:04 字数 308 浏览 9 评论 0原文

我将如何模拟这种情况？数据库应该如何设计？我应该上什么课？

问题陈述：每个员工至少属于一个项目，每个项目有很多任务，每个任务至少分配给一个员工。

我应该能够培养出

从事某个项目的员工。
属于项目的任务
给定员工正在执行的任务。

等等...

循环/循环关系是一个糟糕的设计吗？可以消除吗？

数据库中的实体应如何表示？应该如何使用类来表示实体？

提前致谢，

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千柳 2024-07-29 16:34:04

您没有提到任何有关性能或使用要求的内容，因此我将以通用方式回答，如果您需要更具体的信息，我将更新我的答案。对于数据库表，我建议采用类似的通用标准化方法。

tblProject
    ProjectID
    ProjectDescription etc.

tblTask
    TaskID
    TaskDescription etc.

tblEmployee
    EmployeeID
    Name etc.

tblProjectTasks
    ProjectTasksID
    ProjectID
    TaskID

tblTaskAssignments
    TaskAssignmentsID
    TaskID
    EmployeeID

另一种有效的方法是创建一个定义项目的表和另一个定义项目列表的表。对于任务和员工来说也是如此。在现实世界的应用程序中，这些实体通常在更通用的表中定义良好，就像您可以设计一个包含其他定义良好的对象的类一样。例如，您没有提及员工以外的项目资源。这些资源可以用定义资源类型、资源属性等的模式来表示，然后将资源加入项目和/或任务。

您还可以创建一个代表项目员工的表，但其中的数据将是多余的，因为您可以通过连接其他表来找到分配给项目的员工。恕我直言，只有当表很大并且这种特定类型的查询非常频繁使用时才需要这种重复。但我仍然会首先考虑其他方法。

你还问了关于课程的问题。如果没有更好地理解你的目标，就很难太具体。在典型的 OO 设计中，类应该清楚地表示项目、任务和员工。但您需要对其进行定制以满足您的特定需求。

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.

回复收藏 0 原文

氛圍 2024-07-29 16:34:04

我将尝试以尽可能通用的方式回答您的问题，并避免重复之前答复中的特定表结构。一般来说，实体之间的循环关系并不是一件坏事...相反，它们很常见：

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

虽然一个项目有员工...并且员工也有一个项目（或者，如果员工可以工作，则可能有许多项目）一次进行多个项目）。从数据库的角度来看，当您创建外键时，无论您是否愿意，这种“循环”关系都存在。

更重要的问题是，从概念角度来看，员工是否知道自己属于哪个项目重要吗？虽然项目知道员工正在做什么可能非常重要...但员工了解其工作的项目可能并不重要。这就是所谓的“导航性”，与我们的数据库结构不同，我们可以通过我们的类来控制它。 Project 对象将具有 Employee 对象的集合，但 Employee 对象不一定需要具有 Project 属性（或项目集合）。

关于导航性，我无法为您提供固定答案。这通常是主观的，取决于您的业务需求。如果您建模的业务具有员工知道他们正在从事哪些项目的概念，并且该知识对于完成您的业务逻辑将执行的流程非常重要……那么您需要这种循环关系。员工与任务、项目与任务等之间的导航性也是如此。

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.

回复收藏 0 原文

零度℉ 2024-07-29 16:34:04

从数据库开始并从那里开始工作。我需要更多信息来推荐班级结构。您想要/需要为员工提供物品吗？或者它们会成为项目的财产吗？等等..

数据库设计：

Projects
    ProjectID
    ProjectName...
    EmployeeID

Tasks
    TaskID
    ProjectID
    TaskName...
    EmployeeID

Employees
    EmployeeID
    EmployeeName...

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

回复收藏 0 原文

↘紸啶 2024-07-29 16:34:04

我会为数据库设计做这样的事情：

    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)
)

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)
)

回复收藏 0 原文

~没有更多了~