开发者

Database boolean attributes or FK to definition table?

开发者 https://www.devze.com 2023-02-28 09:34 出处:网络
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:

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 da开发者_StackOverflow中文版ta modeling patterns?


You are thinking about database normalization.

However, you can ensure consistency by implementing a CHECK constraint that will only allow one of the boolean fields to be set to true at any one time.

0

精彩评论

暂无评论...
验证码 换一张
取 消