PostgreSQL Query Catalog

Find column descriptions that are candidates for describing a domain.

Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains.

Find domains that declare a default value and that are used in case of multiple foreign key constraints that point to different tables. Domains should be used in a manner that does not cause unnecessary coupling of concerns. For instance, let us assume that columns client_state_type_code of table Client (that is used to implement the relationship with table Client_state_type) and worker_state_type_code of table Worker (that is used to implement the relationship with table Worker_state_type) have been defined based on the same domain. It the domain has a default value, then it determines the initial state of both clients and workers. However, it must be possible to determine the initial state independently in case of clients and workers.

Use different names to avoid confusion.

Find names of domains that contain the name of the table that column the domain specifies. Domains are reusable artifacts that one should be able to use in case of different base tables. Thus, it is inappropriate to use the name of a particular table in the name of the domain.

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.

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.

Find for each domain the number of usages in base tables. The less you have managed to reuse domains in case of different columns, the more you have wasted your time by creating the domains.

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.

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. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four).

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.

Find cases where you store images and other media as files outside the database and store in the database only paths to the 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.

"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).

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

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

Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion.

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.

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.

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.