| 1 |
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-06-06 09:46 |
MIT License |
View |
| 2 |
Overly strict CHECK constraints on personal names |
This query identifies potentially overly strict CHECK constraints applied to base or foreign table columns that store personal names. It highlights validation rules that might inadvertently block valid legal names. |
Problem detection |
INFORMATION_SCHEMA only |
2026-06-03 12:28 |
MIT License |
View |
| 3 |
CHECK constraints on columns with personal names |
This query identifies CHECK constraints applied to base or foreign table columns that store personal names. It flags these constraints for review to ensure they are not overly strict. Many regular expressions or character limits fail to account for the complexity of real-world names (such as those containing apostrophes, hyphens, or diacritics), thereby inadvertently preventing the registration of valid legal names. |
General |
INFORMATION_SCHEMA only |
2026-06-03 12:10 |
MIT License |
View |
| 4 |
Derived table names have prefix or suffix |
This query identifies views and materialized views that include a prefix or suffix in their names (such as v_ or _mv). In a well-designed database, derived tables should follow the exact same naming conventions as base tables, because they function identically from the application's perspective. Therefore, if base tables do not use type-based prefixes or suffixes, derived tables should not use them either. |
Problem detection |
system catalog base tables only |
2026-06-03 11:50 |
MIT License |
View |
| 5 |
Incorrect use of COUNT(*) with outer joins |
This query identifies user-defined routines and derived tables (views or materialized views) that use the COUNT(*) aggregate function (without a FILTER clause) alongside an OUTER JOIN and a GROUP BY clause. This combination is a common SQL anti-pattern. When grouping with an outer join, COUNT(*) counts the rows themselves, including the NULL-filled rows generated for non-matching records. Consequently, it inaccurately reports a count of 1 for completely empty groups instead of the correct count of 0. To get accurate results, developers should count a specific non-null column from the joined table. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 14:41 |
MIT License |
View |
| 6 |
Multi-column CHECK constraints with unnecessary NULL checks |
This query identifies multiple-column CHECK constraints that explicitly check for NULL values. In general, constraint definitions should be kept as simple as possible. Because a NULL value causes a logical condition to evaluate to UNKNOWN—and CHECK constraints inherently allow rows that evaluate to either TRUE or UNKNOWN—there is usually no need to explicitly allow missing values. For instance, instead of writing CHECK (last_update_time IS NULL OR last_update_time >= creation_time), the constraint should simply be written as CHECK (last_update_time >= creation_time). However, a valid exception exists for implication rules (P ⇒ Q), which can be rewritten as NOT (P) OR Q. For example, the constraint CHECK (NOT (product IS NOT NULL) OR service IS NULL) properly enforces the rule that if a product is present, the service must be NULL. Such logical constructs are entirely appropriate. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 14:29 |
MIT License |
View |
| 7 |
Unnecessary NULL checks in single-column constraints |
This query identifies single-column CHECK constraints (applied either directly to a base table or through a domain) that explicitly check for NULL values. Constraint definitions should be kept as simple as possible. Because a NULL value causes a logical condition to evaluate to UNKNOWN, and CHECK constraints inherently allow rows that evaluate to either TRUE or UNKNOWN, there is no need to explicitly allow missing values. For instance, instead of writing CHECK (price > 0 OR price IS NULL), the constraint should simply be written as CHECK (price > 0). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 13:55 |
MIT License |
View |
| 8 |
Snake_case violations detected by common suffixes |
This query identifies database identifiers (columns, parameters, etc.) that likely violate the snake_case naming convention based on suffix analysis. It flags names ending with common temporal or attributional terms (date, time, by) where the suffix is not immediately preceded by an underscore. This pattern is highly indicative of camelCase (e.g., createdDate, updatedBy) or PascalCase usage. Adhering to snake_case (e.g., created_date, updated_by) is the recommended standard for SQL database schemas to ensure consistency and readability. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 13:50 |
MIT License |
View |
| 9 |
All-caps comments on derived tables and routines |
This query identifies comments on derived tables (views) and routines that are 50 characters or longer and written entirely in uppercase letters. Writing long comments exclusively in capital letters should be avoided, as it significantly reduces readability and is generally perceived as shouting in digital communication. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 13:48 |
MIT License |
View |
| 10 |
Too generic names (tables) |
This query identifies tables with semantically weak, generic names that violate schema design best practices. It flags tables with name components such as "table", "data", "information", or "list". The principle is that a table name should accurately represent the real-world entity it models. Using generic nouns obscures the schema's meaning, reduces readability, and forces developers to inspect the table's contents to understand its purpose. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 11:09 |
MIT License |
View |
| 11 |
Base table has a national identification number as a key |
This query identifies base table columns whose names suggest they store national identification numbers (personal codes), specifically targeting those that serve as a primary or unique key. Using a national ID as a standalone key enforces a highly restrictive business rule: it assumes that all individuals in the system come from a single country, as identical ID numbers can exist in different countries. The query flags these instances to prompt a review, ensuring that this single-country limitation is intentional and actually aligns with the domain rules. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-01 16:05 |
MIT License |
View |
| 12 |
Insufficient length for international personal codes |
This query identifies varchar columns in base tables whose names suggest they store national identification numbers (personal codes), specifically in tables that also include a country code column. This combination implies that the table stores personal codes from various countries. To safely accommodate different international formats, the field size of the personal code column should be at least 20 characters. The query flags any such columns that are too short. |
Problem detection |
INFORMATION_SCHEMA only |
2026-06-01 16:02 |
MIT License |
View |
| 13 |
Inappropriate length constraints for address columns |
This query identifies base table columns designated for storing address components (e.g., IP addresses, emails, telephone numbers, or physical locations) that have inappropriate length constraints. It flags columns where the defined field size is either too strict or too loose compared to real-world data requirements. Operating on a heuristic basis, the query targets columns whose names imply address data (e.g., containing 'addr' or 'mail') but whose definitions fail to align with standard lengths. Highlighting both insufficient allocation (truncation risk) and unbounded allocation (data quality risk) helps ensure that these fields are sized according to domain standards, which is crucial for data integrity and usability. |
Problem detection |
INFORMATION_SCHEMA only |
2026-06-01 15:44 |
MIT License |
View |
| 14 |
Incorrect use of non-deterministic functions in CHECK constraints |
This query identifies CHECK constraints that use non-deterministic time functions (such as now or current_date) in a way that causes initially valid data to become invalid over time. For example, a constraint like localtimestamp(0) > end_date is an anti-pattern because advancing time will eventually violate it. However, safe implementations—such as birth_date < CURRENT_DATE—are permitted, because once the condition is met, it remains true indefinitely as time moves forward. |
Problem detection |
INFORMATION_SCHEMA only |
2026-06-01 15:37 |
MIT License |
View |
| 15 |
Oversized classifier code data types |
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-29 15:53 |
MIT License |
View |
| 16 |
ON UPDATE CASCADE is missing (based on classifier tables) |
This query identifies foreign key constraints referencing classifier tables that lack an ON UPDATE CASCADE clause. Classifier tables hold reference data (e.g., status types, categories). While their rows should not be deleted if they are currently in use, any modifications to their underlying codes should automatically cascade to all dependent tables to maintain data integrity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-29 15:51 |
MIT License |
View |
| 17 |
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-29 15:50 |
MIT License |
View |
| 18 |
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-29 15:49 |
MIT License |
View |
| 19 |
Classifier tables without a name column |
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. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-29 15:47 |
MIT License |
View |
| 20 |
Inconsistent prefixing of specific (non-generic) column names |
This query audits the database for inconsistent column naming conventions, specifically focusing on non-generic, domain-specific attributes (excluding primary keys, foreign keys, and generic fields like 'comment'). It detects a hybrid naming strategy: some columns are prefixed with the table name (e.g., employee_salary) and others are not (e.g., hiring_date). This inconsistency suggests a lack of a standardized data dictionary, making query writing unpredictable. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-26 19:01 |
MIT License |
View |