具有相同“概念”的共享表；但数据不同
我正在开发一个我继承的数据库，并且遇到了一些我不确定是否是一个好的做法。
基本上有一个状态表，其中包含状态ID 和描述的列表。有两个表与其有 FK 关系。两个表之间有共同的状态（基本相同的描述）和一些个别表所特有的状态。
除此之外，还有另一个“类型化”状态表，它仅与单个表（而不是前两个表）相关。
Table1 ---->StatusTable<-----Table2 Table3---->Table3Status
在我看来，“类型化”状态表是在第一个状态表之后一段时间添加的，可能来自不同的开发人员。我的问题是我应该合并第二个状态表并使其在整个数据库中保持一致还是应该分离组合使用表？
我想第三个选项是向状态表“StatusTypeID”添加另一个字段，并将 FK 添加到定义状态的表中。
想法？
编辑： 更具体地说： 这三个表是（我不支持 Tbl 前缀）TblVersionEvaulationFormulaire、TblEvaluationMandat 和 TblRecontreCarriere。 IE TblVersioning、TblMidtermEvaluations、TblCareerInterestsAndExperience。版本控制表跟踪其他两个表的更改。如果中期评估的标准发生变化，我会创建一个新版本并将其添加到版本控制表中。与 TblCareerInterestsAndExperience 相同。例如，所有三个表的状态均为“已发布”，但每个表的状态都是独一无二的。我想我应该分开这些表，或者在状态表中添加第三列来区分。
I'm working on a database that I inherited and I came across something that I am not sure is a good practice.
Basically There is a Status table, with a list of StatusID's and descriptions. There are two tables that have FK relations to it. Between the two tables there are common status (basically the same description) and some that are unique to the individual table.
To add to this, there is another 'typed' Status table that is solely related to a single table (not the first two).
Table1 ---->StatusTable<-----Table2 Table3---->Table3Status
It seems to me that the 'typed' status table was added a while after the first, likely from a different developer. My question is should I combine the 2nd status table and make it consistent across the database or should I separate the combined use table?
I suppose a third option is to add another field to the Status table 'StatusTypeID' with a FK to a table defining the status.
Thoughts?
EDIT:
To be more specific:
The three tables are (and I don't condone the Tbl prefix) TblVersionEvaulationFormulaire, TblEvaluationMandat, and TblRecontreCarriere. IE TblVersioning, TblMidtermEvaluations, TblCareerInterestsAndExperience. The Versioning table tracks both the other two table changes. IE if the criteria of the midterm evaluation changes, I would create a new version of it and add it to the Versioning table. same with TblCareerInterestsAndExperience. So for example, all three tables have status as Published, but each have status unique to them. I am thinking I should either separate the tables, or add a third column to the status table to differentiate.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果它们都是特定类型状态类的状态表，那么最简单的方法是拥有一个单个状态表，并让三个表中的每一个都为其声明外键。
您始终可以向引用状态表的特定表添加约束，以了解哪些状态不适用。您可以通过明确地分解 status-es 来更优雅地做到这一点，以便查询可以成为约束的基础（即，没有硬编码的“IN”子句）。您可能需要在状态表中至少添加一列来帮助将状态类别分解为子类别。
如果 table3 确实使用不同类型的状态，那么它需要自己的表。所有这些都有一个语义元素——您需要问自己特定类型的状态对于特定的表是否可以想象/相关。如果不是，那么整合所有状态可能是不可行的。
例如，在我的系统中，我可能有订单状态和系统状态。这是两个截然不同的类别，我无法想象某个地方的某些订单项可能具有订单状态或系统状态的情况。我这里需要两张桌子。但是，如果我有 ORDER PRE-PROCESSING_STATUS 和 ORDER POST-PROCESSING STATUS，那么它们可能可以合并到一个类中。因此，您需要在此处进行逻辑级别的健全性检查。
If they are all status tables of a particular type of status class, it is simplest to have a single status table and have each of the three tables declare foreign keys to it.
You can always add constraints to a particular table that references the status table in terms of which status(es) are not applicable. You can do this even more elegantly by breaking down the status-es categorically, so that a query can the basis of your constraint (i.e., no hard-coded "IN" clause). You will probably need at least one extra column in your status table to help break down the status classes into sub-categories.
If table3 is really using a different kind of status, then it needs its own table. There's a semantic element to all of this -- you need to ask yourself if a particular type of status is even conceivable/relevant for a particular table. If not, then it may not be feasible to consolidate all statuses.
For example, in my system I might have ORDER STATUS and SYSTEM STATUS. These are two very different classes and I can't imagine a case where some line item somewhere might have either an order status or a system status. I would need two tables here. However, if I had ORDER PRE-PROCESSING_STATUS and ORDER POST-PROCESSING STATUS, those can probably be merged into a single class. So there's a sanity check you'll need to do at a logical level here.
如果 Table3Status 和 StatusTable 是不相交的，那么我会保留它，并将其视为糟糕的命名，因为这意味着它们完全用于不同的目的。
出于显而易见的原因，应该合并表 1 和表 2。
以防万一它有用......我日常工作的错误跟踪数据库有一个分类系统，其中“问题”都在一个表中，并按索引字段类型、用途和产品进行划分。这三个排列的每个排列都可以分配一个自定义状态集，如果没有特定的组合适用于某个问题，那么它会回退到 3 个级别的默认状态集。所有状态集都位于一张表中。
If Table3Status and StatusTable are disjoint then I'd leave that alone and think of it as poor naming only since it would imply they are for different purposes entirely.
Table1 and Table2 should be consolidated for obvious reasons.
Just in case it's useful... A bug tracker database I work on day-to-day at work has a categorisation system where "issues" are all in one table and are divided by indexed fields type, purpose and product. Each permutation of these three can have a custom status set assigned to it, if no specific combination applies to an issue then it falls back through 3 levels of default status sets. All the status sets live in one table.