Discussion on database design decisions

Hello,

Thanks for developing Ralph.

We are part of the database group at Georgia Tech. We are developing a tool, called sqlcheck, for assisting database application developers in designing performant and maintainable applications. sqlcheck automatically detects sub-optimal database design choices and suggests better alternatives. For instance, storing comma-separated values in a column violates the first normal form, which impacts the performance, maintainability, and extensibility of the application.

We extracted SQL queries from Ralph and analyzed them with sqlcheck. Here are the results:

- **Too many indexes/Indexes not being used [Postgres]:** For certain columns (e.g., supports_support.invoice_no, accounts_region.name, etc.), we observed that the application constructs multiple indexes on the same column, i.e., btree index (mostly due to UNIQUE constraint) and a btree index with ‘varchar_pattern_ops’. From the queries obtained by running your integration tests, we feel that the index with ‘varchar_pattern_ops’ is never used by your application. We believe that these indexes are created by Django as a default behavior (refer: [this](https://code.djangoproject.com/ticket/12234), and [this](https://code.djangoproject.com/ticket/24507) ). We observe the tables created by the integration tests have 86 ‘varchar_pattern_ops’ indexes, and we wanted to validate if it’s not because of the default behavior. Our experiments with sqlcheck showed that constructing too many indexes slows down data ingestion and also increases disk usage.

- **Usage of pattern matching:** For pages which provide filters, such as Supports, Back Office Assets, etc. we observed queries with predicates which have a leading and trailing wild card on most of the fields in the filters (eg. UPPER("supports_support"."contract_id"::text) LIKE UPPER('%test1%')). As you might be aware, for such queries, a sequential scan is performed, and the indexes on these columns will not be utilized. We were interested in knowing if such a wild card filter is necessary for most of those fields and if the considered the possibility of (i) not using wildcards (eg. UPPER("supports_support"."contract_id"::text) LIKE UPPER('test1')) or (ii) using trailing wildcards (eg. UPPER("supports_support"."contract_id"::text) LIKE UPPER('test1%')) for a few fields so that indexes can be utilized.

- **Missing database level constraint checks:** For certain columns, (eg. “back_office_backofficeasset”.“status”, “access_cards_accesscard”.“status”), we observe that there is validation at the application level to restrict the values which can be stored in these fields. We believe it’s best practice to have database level checks to make sure data integrity is always maintained. This can be achieved in two ways, (i) through the ‘CHECK’ constraint feature provided by the DBMS or, (ii) [preferred/better] have a new table which stores a list of possible statuses and have a foreign key relationship in the “back_office_backofficeasset” table.

We hope that sqlcheck will assist the broader community of database application developers with designing applications. Your feedback will be greatly appreciated.

Thanks for your time.