Database Development and Optimization - Case Studies

These examples show different types of work covered by my database design, optimization, and migration service: data modeling, PostgreSQL performance work, advanced MySQL queries and procedures, automated data processing, and SQL Server environment deployment.

Competitor, results, and cup database

Competitor, race, and results database connected to Joomla modules

A database was built for the Serbian Open Water Swimming Federation to connect competitors, races, results, cups, and scoring. Its data model became the foundation for automating processes that had previously been handled manually.

  • related records for competitors, races, results, and cups were modeled;
  • Joomla modules were developed for registrations, results, scoring, logs, and reports;
  • HTML templates were prepared for certificates and emails;
  • the new solution was connected to the existing Joomla website and the CacaTajka system.

Completed in Q2 2023 and expanded in Q2 2024.

Client feedback

★★★★★5/5

“Dejan Dozet worked on the website of the Serbian Open Water Swimming Federation, improving competitor registration for upcoming races and automating scoring by connecting databases and displaying previous and current competitions. All praise for the work, dedication, and professionalism!”

PostgreSQL database optimization

An existing PostgreSQL database on a dedicated server met the company's functional requirements, but its operations were becoming increasingly slow. The goal was to improve performance without changing how users worked with the system.

  • the parts of the system causing delays were diagnosed;
  • complex SQL queries were optimized and indexes added to important columns;
  • the PostgreSQL configuration on the dedicated server was tuned;
  • targeted data architecture changes were made without disrupting existing functionality.

Completed in Q3 2022 and expanded in Q4 2022.

MySQL queries and procedures for data science

Advanced MySQL queries and stored procedures were developed for an application that analyzes short-term rental performance. The data was processed so the application could compare properties and prepare datasets for further predictions.

  • CTE Update queries classified records into quartiles and deciles;
  • queries and indexes were adapted for faster processing of larger datasets;
  • static reporting tables were prepared for fast access from the web application.

Completed in Q4 2022 and expanded in Q3 2023.

SQL hospitality revenue statistics

The client needed an SQL stored procedure that would automatically group monthly revenue from different properties into meaningful business seasons. The solution replaced manual calculations with repeatable processing that could accommodate additional properties and longer reporting periods.

  • monthly revenue was grouped by property, period, and seasonal category;
  • the procedure was prepared for automatic execution without manual intervention;
  • the structure supports growth in both the number of properties and the amount of data.

Completed in Q4 2020 and expanded in Q2 2021.

Client feedback

★★★★★5/5

“Dejan is an invaluable member of our team, working on complex database queries and procedures. We completed the tasks we needed to and will be hiring him again!”

Automated SQL Server deployment

A C# script was developed for a repeatable development environment, installing SQL Server Developer edition and its supporting services without requiring a manual walk through the setup interface.

  • silent SQL Server installation was automated;
  • SQL Server Agent was enabled;
  • a Database Mail profile was created for subsequent SMTP configuration;
  • access was restricted to the intended SQL authentication.

Completed in Q4 2020 and expanded in Q3 2021.

Separate case study: GeoPackage to SQL Server migration

Geospatial data migration has a distinct technical and search intent, so it remains a separate case study. The C# application transfers data from SQLite-based GeoPackage (.gpkg) files to Microsoft SQL Server while handling different geospatial types, checking data integrity, and logging errors.

Read the complete GeoPackage to SQL Server migration case study.

If you need to design a new structure, improve existing query performance, or migrate data safely, see my database development and optimization service.