| Goal | This query identifies user-defined database object identifiers that terminate in one or two digits. This naming pattern is a design smell, as it often indicates either a bad database structure or the use of "magic numbers" that obscure the identifier's semantic meaning. Database object names should be fully descriptive and self-documenting. The presence of a numerical suffix necessitates a review to determine if a more descriptive name is required (e.g., renaming report_23 to report_for_year_2023) or if the data model needs to be changed. |
|---|---|
| Notes | The query does not consider objects that are a part of an extension. In the result of the query (b) tags are used to highlight the digits. The query excludes the names where the number in the end refers to hashing algorithms md4, md5, sha1, sha2, or sha3. In case of database objects that are not CHECK constraints and domains the additional requirement to problematic names is that the name should not have any digits before the trailing digits. Thus, for instance, a CHECK constraint name "chk_item_weight_between_10_100" is considered to be non-problematic whereas base table name "Items_2022_1" is considered to be problematic. Names that have certain words like "than" or "least" before the trailing digits are not considered to be problematic. Names that end with four or more consecutive digits are not considered to be problematic. The query considers both names in Estonian and in English. |
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Low Many false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
Numbers in names are permitted if they explain the meaning of the object but not if these are sequence numbers or random numbers.
Collections
This query belongs to the following collections:
Find problems about names
A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
| Name | Description |
|---|---|
| Find problems about names | A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview. |
Categories
This query is classified under the following categories:
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.
Naming
Queries of this category provide information about the style of naming.
| Name | Description |
|---|---|
| Duplication of implementation elements | Queries of this catergory provide information about the duplication of the database objects. |
| Naming | Queries of this category provide information about the style of naming. |
Further reading and related materials:
The use of digits in names is a sign of the following antipatterns from the Bill Karwin's book of SQL antipatterns: Multicolumn Attributes (Chapter 8), Metadata Tribbles (Chapter 9).
| Reference |
|---|
| The use of digits in names is a sign of the following antipatterns from the Bill Karwin's book of SQL antipatterns: Multicolumn Attributes (Chapter 8), Metadata Tribbles (Chapter 9). |