| 1 |
All short cycles (tables) |
Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-27 20:54 |
MIT License |
View |
| 2 |
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 |
| 3 |
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 |
| 4 |
Do not always depend on one's parent |
Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. |
General |
system catalog base tables only |
2021-03-12 15:36 |
MIT License |
View |
| 5 |
Do not always depend on one's parent (INFORMATION_SCHEMA) |
Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list. |
General |
INFORMATION_SCHEMA only |
2021-03-07 10:57 |
MIT License |
View |
| 6 |
Do not assume you must use files |
Find cases where you store images and other media as files outside the database and store in the database only paths to the files. |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-27 16:55 |
MIT License |
View |
| 7 |
Do not assume you must use files (based on user data) |
Find cases where you store images and other media as files outside the database and store in the database only paths to the files. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-03-10 12:58 |
MIT License |
View |
| 8 |
Do not clone columns |
"Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore). |
Problem detection |
INFORMATION_SCHEMA only |
2022-11-28 15:15 |
MIT License |
View |
| 9 |
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 |
| 10 |
Do not create multiple columns for the same attribute |
Find base tables that implement recording multivalued attribute values with the help of repeating group of columns. Find base tables that have more than one columns with the same type and field size and the difference between the columns are the numbers in the column names (column1, column2, etc.). |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-18 15:57 |
MIT License |
View |
| 11 |
Do not format comma-separated lists (based on column names) |
Find, based on column names, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-10 12:57 |
MIT License |
View |
| 12 |
Do not format comma-separated lists (based on default values) |
Find, based on default values, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. |
Problem detection |
INFORMATION_SCHEMA only |
2023-12-30 10:59 |
MIT License |
View |
| 13 |
Do not format comma-separated lists (based on user data) |
Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-03-12 15:02 |
MIT License |
View |
| 14 |
Do not leave out the referential constraints (based on adjacency list design) |
Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-03-18 11:13 |
MIT License |
View |
| 15 |
Do not register age as a number |
Find columns of base and foreign tables that based on the column name and type are used to register age. |
Problem detection |
INFORMATION_SCHEMA only |
2024-11-06 09:55 |
MIT License |
View |
| 16 |
Do not use a generic attribute table |
Find base tables that implement a highly generic database design (EAV design - Entiry-Attribute-Value design), according to which attribute values are recorded in a generic table that contains attribute-value pairs. |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-07 17:40 |
MIT License |
View |
| 17 |
Do not use dual-purpose foreign keys |
Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row. |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-07 10:56 |
MIT License |
View |
| 18 |
Each table is both referencing and referenced table (perhaps there is a cycle in relationships) |
Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-07-01 14:23 |
MIT License |
View |
| 19 |
Logical size of a table |
The table size is the sum of the total size of the simple columns and the total size of the complex columns in the table. In case of SQL databases large base tables in terms of number of columns could be a side effect of the problems with cloned columns or multiple columns for the same attribute. A base table with a low normalization level, which is meant to hold data that corresponds to multiple entity types has typically also relatively large number of columns compared with other base tables. Thus, the normalization level of base tables with a large number of columns should be checked as well. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2022-10-21 16:17 |
MIT License |
View |
| 20 |
Many-to-many relationship types that do not have additional attributes |
Find base tables that implement many-to-many relationship types that do not permit repeating relationships. More specifically find tables that have two or more foreign keys and all the columns of the table are either foreign key columns or a surrogate key column. It could be that during the system analysis a mistake has been made and some attributes of the entity type that represents the relationship type have not been discovered. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-01-21 10:48 |
MIT License |
View |