Perhaps a too simplified state machine

Back to Catalog
Goal Find base table columns with Boolean type that name refers to the possibility that these are used to register as to whether an entity is currently in active state or not. Find the base tables that have exactly one Boolean column. During the system design one should find all the possible states of an entity type that influence the behavior of the information system. Data as to whether an entity is in one of these states should be in the database. Having only two states - active/inactive - is sometimes a too big simplification.
Notes The query considers both column names in English and Estonian.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH boolean_columns AS (SELECT table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE data_type='boolean' AND 
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND 
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
nr_of_boolean_columns_is_1 AS (SELECT table_schema, table_name
FROM boolean_columns
GROUP BY table_schema, table_name
HAVING Count(*)=1)
SELECT table_schema, table_name, column_name
FROM boolean_columns
WHERE (table_schema, table_name) IN (SELECT 
table_schema, table_name
FROM nr_of_boolean_columns_is_1) 
AND column_name~*'(aktiivne|aktuaalne|active)'
ORDER BY table_schema, table_name;
Categories

This query is classified under the following categories:

NameDescription
Boolean dataQueries of this category provide information about truth-values data that is kept in the database.
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
State machineQueries of this category provide information about registration of states of entities in a database.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).