Antipatterns in Database Design

The purpose of the study is to adapt the antipattern method to improve the effectiveness of teaching students in the fields of “Applied Informatics”, “Information Systems and Technologies”, “Software Engineering” to the principles and techniques of relational database design within the framework of relevant disciplines and practices. Training in database design is an important component of the formation of a specialist software developer. The totality of decisions taken in the design process should be based on generally accepted rules, principles and recommendations, as well as on the existing experience in creating and operating information systems, which is difficult to reproduce in the conditions of the university audience.

Materials and methods. An approach focused on the concept of antipatterns – unsuccessful common design solutions will allow increasing the effectiveness of students’ training in database design. The antipattern concept has become widespread following the wide spread use of the concept of a pattern, that is, a common standard solution inscribed in the context of the problem being solved. Both the concept of a pattern and the concept of an antipattern were initially focused on object-oriented software, but later gained recognition in all areas of information technology. The paper proposes a scheme for describing the antipattern, includ ing the name, a brief description, possible causes of occurrence, a list of disadvantages of application in the construction of relational databases, a conceptual model in the form of an ER-diagram (entity-relationship diagram), an example of the appropriate database structure, ways to avoid antipatterns, as well as possible exceptions when using just such a design decision when creating a database may be justified.

Research results. Based on educational, scientific and technical literature and periodicals, as well as on the basis of an analysis of the existing practice of designing information systems, a catalog of common antipatterns for designing relational databases has been compiled and described. Examples of antipatterns: “Jumble of names”, “Duplicate tables”, “False abstraction”, “Non-atomic attributes”, “Duplicate columns”, “Changing data in due course (temporaryfields)”, “Parallel connections”, ““Divine” table”, “Speculative metadata modification”, etc. At the same time, some of the patterns made it possible to illustrate the general principles of relational database design, for example, such as “No sophisticated programming is capable of correcting fatal architectural flaws”. It is shown that the existing unsuccessful solutions are often due to certain prerequisites, for example, the need to perform a hotfix that is, a “temporary” solution, or a premature attempt to improve system performance, or the desire to get some report with one request, avoiding unnecessary connections, etc. In addition to the technical aspects, the relevance of a correct and uniform naming system is shown, the absence of which makes it difficult to maintain and develop the system and leads to unnecessary costs. The proposed approach was tested when teaching students the disciplines “Databases”, “Database design”, “Architecture of information systems”. The students who completed the training improved their understanding of the principles and practices of database structure design. Conclusion. The use of the proposed catalog of antipatterns in the educational process of the university makes it possible to accelerate the training of young specialists in the techniques of designing the structure of relational databases, avoid common mistakes when creating information systems, and improve the overall design culture.

1. Kharrington Dzhen L. Proyektirovaniye relyatsionnykh baz dannykh = Designing Relational Databases. Moscow: Laurie; 2006. 230 p. (In Russ.)

2. Titovskaya N.V., Titovskiy S.N. Methodology for teaching future IT specialists database design and development based on an interactive approach. Vestnik Krasnoyarskogo gosudarstvennogo pedagogicheskogo universiteta im. V.P. Astaf’yeva = Bulletin of the Krasnoyarsk State Pedagogical University named after V. P. Astafiev. 2019; 4(50): 75-87. DOI: 10.25146/1995-0861-2019-50-4-164. (In Russ.)

3. Yakhina Z.T., Osipova A.L., Rizayev I.S. Methodology for designing databases in the learning process. Obrazovatel’nyye tekhnologii i obshchestvo = Educational technologies and society. 2012; 15(1): 525-536. (In Russ.)

4. Ahmad R., Chyi W.A., Sarlan A., Kasbon R. Guiding novice database developers in database schema creation. IEEE Conference on e-Learning, e-Management and e-Services. 2015; (9): 708-715. DOI: 10.1109IC3e.2014.7081243.

5. Aleksander K. Yazyk shablonov. Goroda. Zdaniya. Stroitel’stvo. Per. s angl = Pattern Language. Cities. Buildings. Construction. Tr. from Eng. Moscow: Art. Lebedev Studio; 2014. 1096 p. (In Russ.)

6. Gamma E., Khelm R., Dzhonson R., Vlissides Dzh. Patterny ob»yektno-oriyentirovannogo proyektirovaniya. Per. s angl. A. Slinkin = Patterns of Object-Oriented Design. Tr. from Eng. A. Slinkin. Saint Petersburg: Piter; 202. 448 p. (In Russ.)

7. Allen E. Tipichnyye oshibki proyektirovaniya = Typical Design Mistakes. Saint Petersburg: Piter; 2003. 224 p. (In Russ.)

8. Aruchidi N.A., Kalugyan K.Kh., Shcher bakov S.M. Typical mistakes (antipatterns) of educational and methodological activities at the university. Mezhdunarodnyy nauchno issledovatel’skiy zhurnal = International Research Journal. 2021; 2(104): 13-18. DOI: 10.23670/IRJ.2021.103.2.033. (In Russ.)

9. Kalugyan K.Kh., Shcherbakov S.M. Typical mistakes in presenting the results of final qualification works in the direction of «Applied Informatics». Informatika i obrazovaniye = Informatics and Education. 2016; 5(274): 20-28. (In Russ.)

10. William J. Brown, Hays W. «Skip» McCormick, Scott W. Thomas. AntiPatterns in Project Management. Wiley; 2000.

11. Kucherova K.N Analysis of scalability of databases and their applications at the design stage. Systems analysis in design and management. Sbornik nauchnykh trudov XXI Mezhdunarodnoy nauchno-prakticheskoy konferentsii: v 2-kh tomakh = Collection of scientific papers of the XXI International scientific and practical conference: in 2 volumes (St. Petersburg, June 29-30, 2017). Saint Petersburg: Peter the Great St. Petersburg Polytechnic University; 2017: 287-292. (In Russ.)

12. Rzhavin V.V., Oblomov I.A., Fadeyeva K.N. Using relational database design patterns in higher education practice. Sovremennyye naukoyemkiye tekhnologii = Modern science-intensive technologies. 2022; 10-1: 84–88. DOI: 10.17513snt.39351. (In Russ.)

13. Davidson L. Ten common database design mistakes. RedGate Hub; 2007.

14. Vitacolonna N. Conceptual Design Patterns for Relational Databases. 17th International Conference on Information and Software Technologies (IT 2011); 2011.

15. Karwin B. SQL Antipatterns: Avoiding the Pitfalls of Database. Pragmatic Bookshelf; 2010. 333 p.

16. Blaha M. Patterns of Data Modeling. CRC Press; 2010. 266 p.

17. Kirillov V., Gromov G. Vvedeniye v relyatsionnyye bazy dannykh = Introduction to relational databases. Saint Petersburg: BHV-Petersburg; 2012. 464 p. (In Russ.)

18. Kimball R., Ross M. The Data Warehouse Toolkit The Complete Guide to Dimensional Modeling. 2nd Edition, John Wiley & Sons, New York; 2002. 464 p.

19. Bruks F. Mificheskiy cheloveko-mesyats, ili kak sozdayutsya programmnyye sistemy = The Mythical Man-Month, or How Software Systems Are Created. Saint Petersburg: Piter; 2021. 368 p. (In Russ.)

20. Blaha M. Referential Integrity Is Important For Databases. Modelsoft Consulting Corp; 2005.

21.

22.