就项目生命周期的数据库设计提出建议 [英] Advise on database design for a project lifecycle

问题描述

在我们公司，一个项目开始了它作为一个业务发展的生活，它拥有自己的3阶段生命周期。一旦业务发展结束其生命周期，它可能成为一个完整的项目，然后开始一个全新的三阶段生命周期。但是，有时业务发展从初始阶段开始就不会成长为项目。

考虑到这一切，我决定为业务发展（BD）和项目分开一张表（将它们视为单独的实体），每个实体将其存储在生命周期中的当前阶段。当BD成为Project时，它将被输入到Projects表中。由于该项目仍然需要参考所有与BD相关的领域以及任何新的仅项目专题领域，因此通过FK进行BD表格（基本上继承了BD的所有特征并添加更多）。

所以我想检查这个设计中是否有错误，我错过了，我应该把所有内容放在一个Projects表中，并删除BD表吗？
如何最好地处理这个问题的任何想法？

解决方案

由于您的项目有不同的阶段，您可以设计一个表包含该项目，一个状态字段显示它在什么阶段。那么根据状态字段的值，你可以知道它在商业开发中是否在项目阶段或完成。

为了审计目的，您还可以跟踪不同阶段之间的变化。

In our company, a project begins it's life as a business development which has it's own 3-stage lifecycle. Once the business development ends its lifecycle it may become a full fledged project, which then begins a entirely new 3-stage lifecycle. However sometimes business developments never mature beyond their initial phase into projects.

Considering all this, I decided to have a table for Business Developments (BD) and a seperate table for Projects (treating them as seperate entities), each of which stores it's current stage in the lifecycle. When a BD becomes a Project it gets entered into the Projects table. Since that project still needs to reference all the BD-related fields as well as any of the new Project-only fields, it's doing so via FK to the BD table (essentially inheriting all the traits of a BD and adding more).

So I wanted to check if there's something wrong in this design that I'm missing..maybe I should put everything into one Projects table and remove the BD table altogether? Any thoughts on how to best handle this?

解决方案

Since your project has various stages, you can design a single table to contain the project, with a status field to show what stage it is at. Then depending on the value of the status field, you can know whether its in Business Dev, at project stage or completed.

For auditing purposes you can also track when it changed between the different stages.

这篇关于就项目生命周期的数据库设计提出建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！