Goal This query identifies classifier (reference data) tables that do not contain a name column. Since the primary purpose of a reference table is to provide human-readable labels for standardized codes, lacking a name column often indicates an incomplete database design.
Notes The query identifies classifier tables based on the following heuristics: it finds base tables that have four or less columns and have at most one foreign key. The query only includes tables that name indicates that it is a classifier (reference data) table and it is not a master data table. The query considers both names in Estonian and English.
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 (opens in new tab)
Fixing Suggestion Add a name column to the table.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH fk AS (SELECT
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS target_schema,
c.relname AS target_table
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_class f ON f.oid = o.confrelid
WHERE o.contype = 'f'),
classifiers AS (SELECT A.table_schema, A.table_name 
FROM information_schema.tables A 
INNER JOIN information_schema.schemata S
  ON A.table_schema = S.schema_name
WHERE 
  A.table_type = 'BASE TABLE'
  AND (A.table_schema = 'public' OR S.schema_owner <> 'postgres')  
  AND A.table_name ~* '(klassifikaator|tüüp|tyyp|type|liik|liigid|mark|mudel|varv|kind|seisund|staatus|status|state|kood|code|sõnastik|sonastik|dict|register|loend|nimekiri|seadistus|kategoori|categor|lookup|ref|enum|class|model|color)'  
  AND A.table_name !~* '^[_]*(osapool|party|klient|customer|client|isik|person|kasutaja|user|toode|product|artikkel|article|teenus|service|töötaja|tootaja|employee|partner|ettevõte|ettevote|company|firma|organisatsioon|organization|leping|contract|asukoht|location|aadress|address|projekt|project|osakond|department)[_]*$'
  AND (SELECT Count(*) AS cnt FROM INFORMATION_SCHEMA.columns AS c
WHERE A.table_schema=c.table_schema
AND A.table_name=c.table_name)<=4
AND (SELECT Count(*) AS cnt FROM fk
WHERE A.table_schema=fk.target_schema
AND A.table_name=fk.target_table)<=1
)
SELECT 
table_schema, 
table_name
FROM classifiers
WHERE NOT EXISTS (SELECT *
FROM information_schema.columns AS B
WHERE classifiers.table_schema=B.table_schema
AND classifiers.table_name=B.table_name
AND B.column_name~*'(nimi|nimetus|name)')
ORDER BY table_schema, table_name;

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Classifier tables

Queries of this category provide information about registration of classifiers.

Result quality depends on names

Queries 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.

NameDescription
Classifier tablesQueries of this category provide information about registration of classifiers.
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.