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)
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:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
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.