| 241 |
Multiple columns in the same base table that are associated with a sequence generator |
Find base tables where multiple columns are associated with a sequence generator. Do not create unnecessary sequence generators. If one uses in a table a surrogate key, then it is enough to have one column where the values are generated by using a (external or internal) sequence generator. |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-08 00:41 |
MIT License |
View |
| 242 |
Base tables, which statistics is probably not up to date |
Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 243 |
Empty tables |
Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. |
Problem detection |
system catalog base tables only |
2022-10-21 15:55 |
MIT License |
View |
| 244 |
Base tables where uniqueness is achieved by using only unique indexes |
Find base tables where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-10-21 01:54 |
MIT License |
View |
| 245 |
Base tables with multiple comment columns |
Find base tables with more than one comment columns |
General |
INFORMATION_SCHEMA only |
2021-04-02 12:21 |
MIT License |
View |
| 246 |
Base tables with multiple name columns |
Find base tables with more than one name columns. Perhaps the normalization level of the table is low. |
General |
INFORMATION_SCHEMA only |
2021-04-02 12:21 |
MIT License |
View |
| 247 |
Base tables that have no uniqueness requirement for rows whatsoever |
Find base tables without any unique constraints and primary key as well as any unique index, whether it is created explicitly by a developer or automatically by the DBMS. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-10-21 10:16 |
MIT License |
View |
| 248 |
Base tables that have neither a unique constraint nor the primary key |
Find base tables without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. |
Problem detection |
INFORMATION_SCHEMA only |
2021-02-25 17:30 |
MIT License |
View |
| 249 |
Perhaps incorrect use of 'NULL' |
Find Boolean expressions, queries, routines, and default values that refer to value 'NULL'. Perhaps NULL was intended instead. 'NULL' is a string (a value) but NULL is a special marker for denoting missing value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 13:19 |
MIT License |
View |
| 250 |
B-tree index fillfactor has been explicitly set to 90 |
Find B-tree indexes that fillfactor has been explicitly set to 90. In case of B-tree indexes the default is 90. |
Problem detection |
system catalog base tables only |
2024-12-11 14:37 |
MIT License |
View |
| 251 |
Too generic names (candidate key columns) |
Find candidate key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix. These are too generic names. |
Problem detection |
system catalog base tables only |
2023-01-20 12:35 |
MIT License |
View |
| 252 |
Do not clone tables |
Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.). |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-18 14:43 |
MIT License |
View |
| 253 |
Unique index definition instead of a key declaration |
Find cases where a unique constraint (that is not case-insensitive or partial, i.e., applies only to certain rows) has been enforced by using a CREATE UNIQUE INDEX statement instead declaring a PRIMARY KEY, UNIQUE, or EXCLUDE constraint. You should try to work on as high level of abstraction as possible. According to the ANSI-SPARC Architecture indexes are a part of internal database schema whereas constraints are a part of conceptual schema, i.e., at the higher level of abstraction. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 254 |
Mixing Concat and || |
Find cases where different means are used to concatenate text within the same object. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-03 14:36 |
MIT License |
View |
| 255 |
Mixing Concat and Coalesce |
Find cases where different means are used to deal with NULLs in case of concatenating texsts. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-08 14:25 |
MIT License |
View |
| 256 |
Enumerated or range types with the same name in different schemas |
Find cases where in different schemas there are enumerated or range types with the same name. Types are like words that can be used to construct generalized claims about the real world (table predicates). Better not to duplicate the words in the dictionary. Also make sure that this is not a duplication. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 257 |
Perhaps CHECK should be associated with a domain |
Find cases where multiple columns with the same domain have exactly the same CHECK constraint that is directly associated with the table. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-11-23 15:03 |
MIT License |
View |
| 258 |
Multiple tables share the same state classifier |
Find cases where multiple tables share the same state classifier. For each main entity type one should create a separate state classifier table. Even if the classifier values are the same in case of two entity types for now these may become different in the future. Having a shared state classifier table usually means very simplistic state machines (states active and inactive) that could point to the gaps in analysis. |
Problem detection |
system catalog base tables only |
2023-12-30 15:51 |
MIT License |
View |
| 259 |
Optional columns before mandatory columns |
Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. |
Problem detection |
INFORMATION_SCHEMA only |
2021-02-25 17:29 |
MIT License |
View |
| 260 |
Some CHECKS are associated with a domain and some with the base table columns that have the domain |
Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:29 |
MIT License |
View |