Database boolean attributes or FK to definition table?
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.
精彩评论