Seq nr | Name | Goal▲ | Type | Data source | Last update | License | ... |
---|---|---|---|---|---|---|---|
221 | Perhaps a CHECK constraint about required personal name components is missing | Find base tables that have optional columns for recording both given name and surname and do not have a CHECK constraint that requires that at least one of the name components must be registered in case of each person. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-10-31 01:16 | MIT License | |
222 | Perhaps a unneccessary surrogate key | Find base tables that have the primary key that is not a surrogate key and an alternate key that is a surrogate key. Perhaps the surrogate key column is not needed. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 20:59 | MIT License | |
223 | The name of the base table that implements a binary relationship type does not explain the meaning of the relationship | Find base tables that implement a binary relationship type (have two foreign keys) and the name of the table consist of the names of tables that this (intermediate) table connect. The names should be derived from the domain. For instance, instead of name Course_Lecturer it is better to have name Teaching. | Problem detection | system catalog base tables only | 2023-03-18 17:58 | MIT License | |
224 | 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 | |
225 | 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 | |
226 | Many-to-many relationship types that do not permit repeating relationships and 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 belong to a foreign key. In addition, all the table columns must be covered by a primary key or unique constraint. 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 | 2021-03-28 12:04 | MIT License | |
227 | 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 | |
228 | Prefixes of base table names | Find base tables that name starts with a prefix. Do not use prefixes in case of base table names. Derive the names from the names of entity types. Do not use "_", "t_", "tab_", "t11_" etc as prefixes of a table. | Problem detection | INFORMATION_SCHEMA only | 2022-11-15 16:32 | MIT License | |
229 | All the non-primary key columns are optional | Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
230 | Base tables where all the columns are optional | Find base tables where all the columns are optional, i.e., permit NULLs. In such tables can be rows with no identity value and thus indistinguishable from other rows. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
231 | All columns of a base table have a default value | Find base tables where all the columns have a default value. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |
232 | All unique keys have at least one optional column | Find base tables where all unique keys (sets of columns covered by a unique constraint, or a unique index) have at least one optional column. In this case there can be rows in the table where the values that should identify the row are missing. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL, b INTEGER, CONSTRAINT ak_uniq UNIQUE (a, b)); INSERT INTO Uniq(a, b) VALUES (1, NULL); INSERT INTO Uniq(a, b) VALUES (1, NULL); | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 11:54 | MIT License | |
233 | Perhaps a redundant column (based on sequence generators) | Find base tables where more than one column gets the default value by using the sequence generator mechanism. | Problem detection | INFORMATION_SCHEMA only | 2021-03-05 09:42 | MIT License | |
234 | 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 | |
235 | 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 | |
236 | 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 | |
237 | 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 | |
238 | 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 | |
239 | 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 | |
240 | 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 |