PostgreSQL Query Catalog

Depth of relational tree of a table T (DRT(T)) is defined by Piattini et al. (2001) as "the longest referential path between tables, from the table T to any other table in the schema". The result may help to classify the data. If the depth is 0, then probably the table contains classifers. Tables with the largest depth probably contain some extra information about main entities.

Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp.

Domain is a reusable artifact. Effort of its creation should be paid off by the advantages that it offers. If a domain is used in case of at most one column of a base table or even if it is used in case of more than one column but it does not specify neither a default value nor a check constraint, then there is no point of creating the domain.

Domains 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.

Domains are reusable artifacts. By associating a domain with a sequence generator, one essentially starts to share sequence generators between tables. It may cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently, i.e., it increases coupling between tables.

Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables.

Do not cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently (for instance the owner column or step), i.e., it increases coupling between tables. By having a shared sequence it is impossible to specify the owner (table column) to the sequence generator.

Do not cause potentially infinite loops. Recursive rules would fire itself over and over again. Although the system is able to detect these after executing a data modification statement it is better to avoid creating these altogether.

Do not create unnecessary sequence generators.

Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values.

Do not have in a database elements that are not useful. PostgreSQL permits tables with no columns. Such tables can be used to implement Boolean variables (tables TABLE_DEE and TABLE_DUM). On the other hand, such tables might be a result of database evolution, where developers have not noticed that they have dropped all the columns of a table or have not noticed that they have created such a table in the first place.

Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Do not let the system to do extra work. Checking a constraint with an AFTER trigger means that the trigger procedure will be executed after the data modification and if the check fails, then the system has to do extra work to roll back the changes.

Do not let the system to do extra work. Logging changes with a BEFORE trigger means extra work for rolling back the changes in case the logged data modification fails.

Do not write incorrect code. Variable NEW: "Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations." (PostgreSQL documentation)

Do not write incorrect code. Variable OLD: "Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations." (PostgreSQL documentation)

Each column should have the most appropriate data type. Developers sometimes forget to use SMALLINT type even if it is logically the best choice. Developers also sometimes forget to use BOOLEAN type and instead invent something.

Each column should have the most appropriate data type. Developers sometimes misuse BIGINT, INTEGER, TEXT or VARCHAR type in places where a type that permits smaller values would be more appropriate and semantically descriptive.

Each column should have the most appropriate data type. If one does not want to have in a table the surrogate key that values are generated by using a sequence generator, then one may use instead a column with Universally Unique Identifiers as the key column.