问题描述
我当前的数据库设计在屏幕截图中.这似乎有缺陷，因为 t_criteria_director 和 t_criteria_ab c d e f... 表之间没有强制关系(pk/fk).是否有更好的方法来设计这种一对多场景以保持参照完整性?
My current db design is in the screenshot. It seems flawed because there is no enforced relationship (pk/fk) between the t_criteria_director and the t_criteria_a b c d e f... tables. Is there a better way to design this one to many scenario to keep referential integrity?
推荐答案
你的设计看起来"没什么，因为我们无法读懂你的想法.您已经给出了设计的某些方面，但没有给出它代表/实现/描述的业务场景"或它是如何实现的.
Your design doesn't "seem" anything because we can't read your mind. You have given some aspects of a design but not the business "scenario" it represents/implements/describes or how it does so.
SQL NULL、UNIQUE、PKs &FK 是一种约束.约束是对可以出现的数据库值的限制.SQL FK 表示表中列列表的子行值必须出现在列列表的其他地方，该列列表的列在其表中形成 SQL UNIQUE NOT NULL 列集(PK 就是这种情况).如果您的设计受到约束并且其他强制约束没有暗示它，请强制执行它.否则不要.最好是声明式的.大多数 SQL DBMS 只允许您声明几种执行成本低的约束.其他必须通过触发器强制执行.
SQL NULL, UNIQUE, PKs & FKs are kinds of constraints. A constraint is a limitation on what database values can appear. An SQL FK says subrow values for a column list in a table must appear elsewhere for a column list whose columns form a SQL UNIQUE NOT NULL column set (which PK is a case of) in their table. If your design is subject to a constraint and it isn't implied by other enforced constraints, enforce it. Otherwise don't. Preferably declaratively. Most SQL DBMSs only let you declare a few kinds of cheap-to-enforce constraints. Others must be enforced via triggers.
约束是在给定情况下(表的谓词，表的含义")以及什么情况可以&不能根据您的业务规则出现.除非你告诉我们，否则我们不知道那些是什么.我们可以希望使用您的表来猜测 &列名称，您提供的任何其他信息 &常识.
The constraints are a consequence of the criteria for rows going into vs staying out of tables in a given situation (the table predicates, "what the tables mean") and what situations can & can't arise according to your business rules. We don't know what those are unless you tell us. We can hope to guess using your table & column names, any other info you give & common sense.
您必须告诉我们约束条件或谓词&什么情况可以/不能出现.
You have to tell us either the constraints or the predicates & what situations can/can't arise.
这里你的表格使用了一个简单的表格加上一些 EAV 设计来记录一些在你的设计中没有明确显示的简单表格的数据.与往常一样，您可以通过仅使用 DDL 来保持简单表的架构 & 来避免 EAV.约束是最新的，但您选择了一个静态模式，它需要更复杂的模式、查询和;约束.EAV 约束的直接表达通常是它们表示的直接表具有某些约束，加上 t_criteria_x 是它的视图和/或它是它们的视图.但通常唯一可用的 SQL 声明只是让您表达其中的片段.
Here your tables are using a straightforward table plus some EAV design to record the data of some straightforward table not explicitly in your design. As always you could avoid EAV by just using DDL to keep the straightforward table's schema & constraints up to date, but instead you have chosen a static schema that requires more complex schema, queries & constraints. The straightforward expression of the EAV constraints is typically that the straightforward table they represent has certain constraints plus that the t_criteria_x are views of it and/or that it is a view of them. But typically the only SQL declarations available just let you express fragments of that.
我猜测您在这里的意图包括对于每个 t_criteria_x 表，其 PK 值必须出现在 t_criteria_director 中，表名称值为t_criteria_x".另一种说法是，如果您向 t_criteria_x 添加了值为 't_criteria_x' 的 table_name 列，那么结果必须有 (id, table_name) 子行显示为 t_criteria_director (criteria_id, table_name) 子行.如果也 t_criteria_director (criteria_id, table_name) 子行是SQL UNIQUE NOT NULL，那么我们认为增强的t_criteria_x 有一个复合SQL FK (id, table_name) 引用t_criteria_director (criteria_id, table_name).您可以通过这样(可能是计算/生成/计算)列实际增加 t_criteria_x 来声明性地表达这一点.但您可能还有其他约束，例如 t_criteria_director 中没有任何 (constraint_id, table_name) 对未被某些增强的 t_constraint_x 引用.
I guess that what you intend here includes that for each t_criteria_x table its PK value must appear in t_criteria_director with a table_name value 't_criteria_x'. Another way to put this is that if you added to t_criteria_x a table_name column with value 't_criteria_x' then the result must have (id, table_name) subrows appear as t_criteria_director (criteria_id, table_name) subrows. If also t_criteria_director (criteria_id, table_name) subrows are SQL UNIQUE NOT NULL then we have that that augmented t_criteria_x has a composite SQL FK (id, table_name) referencing t_criteria_director (criteria_id, table_name). You can express this declaratively by actually augmenting t_criteria_x by such a (possibly computed/generated/calculated) columns. But you probably also have other constraints, like there aren't any (constraint_id, table_name) pairs in t_criteria_director that aren't referenced by some augmented t_constraint_x.
调用列 table_name 显示了来自 EAV 的面向实现的偏见，因为该列在 ER 意义上是一个类型/变体鉴别器/标签，即由 t_criteria_x 中的 id 表示的实体类型表是由 t_criteria_director 表示的实体类型的子类型".(这也是 3GL 记录数据结构中用于动态模拟输入的概念/技术.)毕竟 table_name 列值不必是表名，它只需要是一些标识实体子类型的值，并且这些实体不必只参与一张表的关系/关联.(研究 SQL/数据库/ER 子类型/多态性/继承和设计反模式二/多/多 FKs [原文如此] 到二/多/多表.)
Calling the column table_name shows an implementation-oriented bias from the EAV because that column is a type/variant discriminator/tag in the ER sense that the types of entities represented by the ids in the t_criteria_x tables are "subtypes" of the type of entity represented by t_criteria_director. (This is also a concept/technique from 3GL record data structures used for dynamically simulating typing.) After all the table_name column value doesn't have to be a table name, it just has to be some value that identifies the entity subtype, and such entities don't have to only participate in one table's relationship/association. (Research SQL/database/ER subtyping/polymorphism/inheritance and the design anti-pattern two/many/multiple FKs [sic] to two/many/multiple tables.)
您必须确定表谓词是什么以及它们的约束是什么.最好通过确定它们共同代表的简单表是什么.它的谓词是什么以及数据库约束正在使用它.然后，您必须决定是否要根据成本/收益来修改您的设计以使约束具有声明性和/或是否要通过触发器强制执行约束.
You have to determine what the table predicates are and what their constraints consequently are. Preferably via determining what the straightforward table they collectively represent is & what its predicate is and what the database constraints are using it. Then you must decide whether per cost/benefit you are going to modify your design to make constraints declarative and/or you are or are not going to enforce constraints via triggers.
这篇关于有没有办法在以下场景中保持数据库关系(pk/fk)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！