| 1 |
A state machine is implemented with the help of a state classifier table |
Find implementations of state machines that use a state classifier table. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 2 |
Coupling of distinct entity lifecycles via shared state classifiers |
This query identifies a potential domain modeling flaw where multiple distinct entity tables reference a single, shared state classifier table. According to robust design principles, each main entity type should define its own independent state machine and lifecycle. Sharing a classifier creates undesirable coupling; even if the state vocabularies (e.g., 'Active', 'Inactive') appear identical currently, the business logic for distinct entities is likely to diverge over time. Furthermore, reliance on a universal state table often indicates an under-analyzed domain model utilizing overly generic state transitions. |
Problem detection |
system catalog base tables only |
2026-01-19 17:36 |
MIT License |
View |
| 3 |
Do not leave out the referential constraints (based on classifiers) |
Find non-key and non-foreign columns of base tables with a textual column and small field size in case of which there is a table with the name that is similar to the column name. Perhaps the table is a classifier table and the column should have a foreign key constraint referencing to the table. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 4 |
Excessive data types for classifier codes |
This query identifies state, type or category code columns that use unnecessarily large numeric data types, specifically integer or bigint. It locates these columns by matching specific naming patterns in English and Estonian. Since reference tables typically contain a limited number of rows, these code columns should ideally be defined as smallint to optimize storage space and improve performance. |
Problem detection |
INFORMATION_SCHEMA only |
2026-05-09 12:58 |
MIT License |
View |
| 5 |
Find columns that require new lookup tables (based on names and field sizes) |
This query detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-09 12:15 |
MIT License |
View |
| 6 |
ON DELETE CASCADE is not needed (based on classifier tables) |
This query identifies foreign key constraints with ON DELETE CASCADE that reference classifier tables. Classifier tables hold reference data (e.g., status types, categories), and their rows should not be deleted if they are in use. Applying ON DELETE CASCADE to such a relationship is a critical design flaw, as it creates a direct path for the deletion of a single lookup value to trigger the mass deletion of operational business data. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-16 14:00 |
MIT License |
View |
| 7 |
One true lookup table |
Find tables that contain all (or most) of the classifier values and tables that refer to these. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 8 |
Oversized classifier code data typess |
This query identifies classifier tables where the code column uses a data type that accommodates unnecessarily large values for reference data. Specifically, it flags code columns defined as integer, bigint, or varchar(n) where n > 10. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-16 14:00 |
MIT License |
View |
| 9 |
Potentially a classifier is missing (based on field sizes) |
Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 10 |
Semantic mismatch: non-textual data types for classifier codes |
This query identifies a semantic mismatch in data types for columns intended to store standard codes, such as country, language, currency, or airport codes. It flags columns whose names suggest they contain these codes, but which are defined using non-text data types (e.g., integer, numeric, bigint). Since these codes often contain letters or leading zeros and are not used in mathematical calculations, they are semantically strings. Storing them as numeric types can lead to data loss—such as the truncation of leading zeros—and reduces formatting flexibility. |
Problem detection |
INFORMATION_SCHEMA only |
2026-05-09 12:55 |
MIT License |
View |
| 11 |
Surrogate keys in classifier tables |
This query identifies potential classifier (or reference) tables that contain a column populated by a sequence generator. In good database design, tables storing standard reference data should typically use natural keys rather than auto-incrementing surrogate keys. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-16 14:00 |
MIT License |
View |