Skip to main content
SpringerLink
Log in
Book cover

Computer Science On-line Conference

CSOC 2022: Software Engineering Perspectives in Systems pp 309–324Cite as

On the Design of Base Tables in the SQL Databases of Some Existing Software

  • Conference paper
  • First Online:

Part of the book series: Lecture Notes in Networks and Systems ((LNNS,volume 501))

Abstract

We investigated the quality of database conceptual schemas of PostgreSQL databases of four existing programs, three of which were open source. We automated the process with a set of system catalog-based queries that we have also published (https://github.com/erki77/database-design-queries). We presented 31 SQL database design problems about the data types, field sizes, and default values of base tables as well as sequence generators and the structure of base tables. All these problems appeared at least once in at least one of the databases. Most of the problems (55%; 17) were not mentioned in the reviewed literature. We explained different signs that these problems exist in a database. We explained how to detect the occurrences of the problems by using queries. System catalog-based queries can be efficiently used to continuously check the database design and thus reduce the delay in finding the problems and fixing these.

This is a preview of subscription content, log in via an institution.

Chapter
USD   29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD   189.00
Price excludes VAT (USA)
  • Available as EPUB and PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD   249.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Learn about institutional subscriptions

References

  1. Foidl, H., Felderer, M., Biffl, S.: Technical debt in data-intensive software systems. In: 45th Euromicro Conference on Software Engineering and Advanced Applications, pp. 338–341. IEEE (2019). https://doi.org/10.1109/SEAA.2019.00058

  2. Eessaar, E.: Automating detection of occurrences of PostgreSQL database design problems. In: Robal, T., Haav, H.-M., Penjam, J., Matulevičius, R. (eds.) DB&IS 2020. CCIS, vol. 1243, pp. 176–189. Springer, Cham (2020). https://doi.org/10.1007/978-3-030-57672-1_14

    Chapter  Google Scholar 

  3. Eessaar, E.: The usage of declarative integrity constraints in the SQL databases of some existing software. In: Silhavy, R. (ed.) CSOC 2021. LNNS, vol. 230, pp. 375–390. Springer, Cham (2021). https://doi.org/10.1007/978-3-030-77442-4_33

    Chapter  Google Scholar 

  4. Date, C.J.: An Introduction to Database Systems, 8th edn. Pearson, Addison Wesley, Boston (2003)

    Google Scholar 

  5. Karwin, B.: SQL Antipatterns. Avoiding the Pitfalls of Database Programming. The Pragmatic Bookshelf (2010)

    Google Scholar 

  6. Blaha, M.: A retrospective on industrial database reverse engineering projects - part 2. In: Eighth Working Conference on Reverse Engineering, pp. 147–153. IEEE (2001). https://doi.org/10.1109/WCRE.2001.957818

  7. Blaha, M.R., Premerlani, W.J.: Observed idiosyncracies of relational database designs. In: 2nd Working Conference on Reverse Engineering, pp. 116–125. IEEE (1995). https://doi.org/10.1109/WCRE.1995.514700

  8. Al-Barak, M., Bahsoon, R.: Database design debts through examining schema evolution. In: 8th International Workshop on Managing Technical Debt, pp. 17–23. IEEE (2016). https://doi.org/10.1109/MTD.2016.9

  9. Delplanque, J., Etien, A., Auverlot, O., Mens, T., Anquetil, N., Ducasse, S.: CodeCritics applied to database schema: Challenges and first results. In: 24th International Conference on Software Analysis, Evolution and Reengineering, pp. 432–436. IEEE (2017). https://doi.org/10.1109/SANER.2017.7884648

  10. Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55–64. ACM (2018). https://doi.org/10.1145/3183519.3183529

  11. Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345 (2020). https://doi.org/10.1145/3318464.3389754

  12. Factor, P.: SQL Code Smells. Redgate. http://assets.red-gate.com/community/books/sql-code-smells.pdf. Accessed 29 Dec 2019

  13. Balogh, G., Gergely, T., Beszédes, Á., Szarka, A., Fábián, Z.: Capturing expert knowledge to guide data flow and structure analysis of large corporate databases. Acta Polytech. Hungar. 16(4), 7–26 (2019)

    Google Scholar 

  14. FusionForge. https://github.com/fusionforge/fusionforge. Accessed 26 Oct 2020

  15. LedgerSMB. https://github.com/ledgersmb/LedgerSMB. Accessed 26 Nov 2020

  16. OTRS/otrs. https://github.com/OTRS/otrs/tree/rel-6_0. Accessed 04 Nov 2020

  17. Stansoft. http://www.stansoft.org/download.html. Accessed 21 Nov 2020

  18. Sein, M.K., Henfridsson, O., Purao, S., Rossi, M., Lindgren, R.: Action design research. MIS Quart. 35, 37–56 (2011). https://doi.org/10.2307/23043488

    Article  Google Scholar 

  19. McDonald, K., Matts, C.: The Seven Information Smells of Domain Modelling, InfoQ (2013). https://www.infoq.com/articles/seven-modelling-smells/. Accessed 17 Mar 2022

Download references

Author information

Authors and Affiliations

  1. Department of Software Science, Tallinn University of Technology, Tallinn, Estonia

    Erki Eessaar

Authors
  1. Erki Eessaar
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Erki Eessaar .

Editor information

Editors and Affiliations

  1. Faculty of Applied Informatics, Tomas Bata University in Zlin, Zlin, Czech Republic

    Radek Silhavy

Rights and permissions

Reprints and permissions

Copyright information

© 2022 The Author(s), under exclusive license to Springer Nature Switzerland AG

About this paper

Check for updates. Verify currency and authenticity via CrossMark

Cite this paper

Eessaar, E. (2022). On the Design of Base Tables in the SQL Databases of Some Existing Software. In: Silhavy, R. (eds) Software Engineering Perspectives in Systems. CSOC 2022. Lecture Notes in Networks and Systems, vol 501. Springer, Cham. https://doi.org/10.1007/978-3-031-09070-7_26

Download citation

Publish with us

Policies and ethics

Search

Navigation