Query goal: | Names should be expressive. Find views that name is shorter than the average length of the the names of its directly underlying tables (both base tables and derived tables). |
Notes about the query: | In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Low (Many false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Rename the view to give it more descriptive name. |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT view_schema, view_name, Round(Avg(Length(table_name)),1) AS average_length_of_table_names, Count(*) AS number_of_underlying_tables, string_agg(table_schema || '.' || table_name, ';<br>' ORDER BY table_schema, table_name) AS underlying_tables FROM Information_schema.view_table_usage WHERE view_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) GROUP BY view_schema, view_name HAVING Round(Avg(Length(table_name)),1)>=length(view_name) ORDER BY view_schema, view_name; |
Category name | Category description |
---|---|
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Naming | Queries of this category provide information about the style of naming. |
Reference |
---|
The corresponding code smell in case of cleaning code is "N5: Use Long Names for Long Scopes". (Robert C. Martin, Clean Code) |
Smell "Meaningless name": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018). |