SQL antipatterns detection and database refactoring process

Abstract

SQL antipatterns are frequently-made missteps that are commonly found in the design of relational databases, the use of SQL, and the development of database applications. They are intended to solve certain problems but will eventually lead to other problems. The motivation of this paper is how to assist database administrators in diagnosing SQL antipatterns and suggest refactoring techniques to solve the antipatterns. Specifically, we attempt to automate the detection of logical database design antipatterns by developing a tool that uses Transact-SQL language to query and analyze the database schema. The tool reports on potential antipatterns and gives an instruction on how to refactor the database schema. In an evaluation based on three databases from the industry, the performance of the tool is satisfactory in terms of recall of the antipatterns but the tool detects a number of false positives which affect its precision. It is found that SQL antipatterns detection still largely depends on the semantics of the data and the detection tool should rather be used in a semi-automated manner, i.e it can point out potential problematic locations in the database schema which require further diagnosis by the database administrators. This approach would be useful especially in the context of large databases where manual antipatterns inspection is very difficult.