数据库布尔属性或 FK 到定义表？
在我审查过的遗留代码中，我发现了一种数据模型，它创建相关属性的布尔字段，其中只有一个预计为真。例如：
create table MyTable (
id int primary key not null,
// more fields...
has_x bool not null,
has_y bool not null
);
这是愚蠢的，因为如果两者都设置为 true，则可能会出现不一致的数据。我试图向技术用户（但非开发人员）解释，但不确定如何解释为什么当原始设计“有效”时，将定义更改为一对多关系是正确的，如下所示。
create table Attributes ( -- contains "x" and "y" records.
id int primary key not null,
name varchar(100) not null
);
create table MyTable (
id int primary key not null,
// more fields
attribute_id int not null foreign key references Attributes(id)
);
这些数据建模模式有术语吗？
In legacy code I've reviewed, I've found a data model that create boolean fields of relevant attributes where only one is expected to be true. For example:
create table MyTable (
id int primary key not null,
// more fields...
has_x bool not null,
has_y bool not null
);
This is silly because it allows potentially inconsistent data if both are set to true. I'm trying to explain to technical, but non-developer, users, but not sure how to explain WHY it is proper to change to a 1-to-many relationship to a definition, like below, when the original design "works".
create table Attributes ( -- contains "x" and "y" records.
id int primary key not null,
name varchar(100) not null
);
create table MyTable (
id int primary key not null,
// more fields
attribute_id int not null foreign key references Attributes(id)
);
Is there a term for these data modeling patterns?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您正在考虑数据库规范化。
但是，您可以通过实施
CHECK约束来确保一致性，该约束只会允许在任何时候将布尔字段之一设置为 true。You are thinking about database normalization.
However, you can ensure consistency by implementing a
CHECKconstraint that will only allow one of the boolean fields to be set to true at any one time.