Design of the architecture and data model for an information and analytical water resources monitoring system
DOI:
https://doi.org/10.34185/1562-9945-5-166-2026-22Keywords:
database architecture, bitemporal modeling, ecological monitoring, CQRS, materialized views, audit trail, clustered columnstore indexes, interval algebraAbstract
Environmental data management at enterprises is characterized by a high degree of decentralization, dominated by disjointed spreadsheets and manual aggregation of indicators. This approach significantly complicates the prompt detection of excessive discharges, increasing the risk of significant financial penalties for companies. When attempting automation, an architectural dilemma arises: ensuring environmental compliance requires maintaining a continuous audit trail in rigidly normalized structures, while risk management dictates the need for rapid generation of multidimensional analytical reports, the performance of which degrades under a high level of normalization.
An analysis of engineering approaches highlights the limitations of classical relational models with destructive operations for preserving historical context. Studies suggest using bitemporal modeling as a means of recording chronology. However, this encounters the problem of computational degradation during spatiotemporal joins, as classical indexes are not adapted to interval algebra. The application of the CQRS pattern is considered a way to separate write and read operations. The aim of the work is to develop the architecture and data model of a centralized system that balances the trade-off between the requirement for the integrity of retrospective arrays and the need for high performance of analytical queries.
To achieve this goal, the problem domain was segmented into reference, topological, regulatory, and transactional spaces. The core of the architecture is the implementation of the bitemporal table standard. The variability of environmental legislation is handled through validity period attributes, ensuring the correctness of retrospective calculations. Asymmetric loading is distributed using the CQRS pattern and the Mediator dispatcher, which physically isolated transactions from analytics. To accelerate interval computations, clustered columnstore indexes were integrated. Segment elimination algorithms and batch processing minimized array scanning and ensured data compression. Reading is performed through materialized views with an asynchronous update mechanism. To reduce the risk of generating outdated reports, a deferred reading algorithm with a recorded query timestamp was developed.
The developed model establishes the technological foundation for utilizing the environmental monitoring system as an operational control tool. Abandoning destructive changes in favor of bitemporality ensures data traceability, and workload isolation minimizes the probability of transactional locks. This establishes an infrastructural foundation for timely response to incidents and the reduction of enterprise financial risks.
References
1. Kireitseva, H., Tsyhanenko-Dziubenko, I., Zamula, I., & Demchuk, L. (2024). Analiz stanu ta monitorynh poverkhnevykh vodnykh obiektiv Chernihivskoi oblasti [Analysis of the state and monitoring of surface water bodies of Chernihiv region]. Visnyk KrNU imeni Mykhaila Ostrohradskoho, 1(144), 84–91. DOI: https://doi.org/10.32782/1995-0519.2024.1.11.
2. Joy-Camacho, W., & Thornhill, I. (2024). Opportunities and limitations to environmental management system (EMS) implementation in UK SMEs. Journal of Environmental Management, 367, 121749. DOI: https://doi.org/10.1016/j.jenvman.2024.121749.
3. Vasenko, O. G., Bruk, V. V., & Svyrydov, Y. (2020). Ekolohichnyi monitoryng vodnykh resursiv [Ecological monitoring of water resources]. Eastern-European Journal of Enterprise Technologies, 6(10(108)), 6–13. DOI: https://doi.org/10.15587/1729-4061.2020.219556.
4. Vlasova, O., Shevchenko, I., & Kozytskyi, O. (2026). Kartohrafichne vidobrazhennia ekolohichnoho stanu poverkhnevykh vodnykh obiektiv Pravoberezhzhia Ukrainy [Cartographic representation of the ecological state of surface water bodies of the Right Bank of Ukraine]. In Vodna bezpeka: naukovi rishennia v umovakh kryz ta transformatsii: zbirnyk tez XIV Mizhnarodnoi naukovo-praktychnoi konferentsii (pp. 58–60). IVPIM NAAN. DOI: https://doi.org/10.31073/mivg2026.
5. Chau, V. T. N., & Chittayasothorn, S. (2021). A bitemporal SQL database design method from the enhanced entity-relationship model. In 2021 7th International Conference on Engineering, Applied Sciences and Technology (ICEAST) (pp. 85–90). IEEE. DOI: https://doi.org/10.1109/ICEAST52143.2021.9426270.
6. Gnedina, K. V. (2021). Suchasni pidkhody do upravlinnia vodnymi resursami ta mekhanizmy yikh ekolohichnoho monitorynhu [Modern approaches to water resources management and mechanisms of their ecological monitoring]. Ekolohichni nauky, (4(37)), 33–39. DOI: https://doi.org/10.32846/2306-9716/2021.eco.4-37.4.
7. Henneberg, J., Schuhknecht, F. M., Reutter, P., Brast, N., & Spichtinger, P. (2022). Northlight: Declarative and optimized analysis of atmospheric datasets in SparkSQL. In SSDBM '22: Proceedings of the 34th International Conference on Scientific and Statistical Database Management (Article 13). Association for Computing Machinery. DOI: https://doi.org/10.1145/3538712.3538715.
8. Iraqi, O., El Kadiri El Hassani, M., & Zouine, A. (2025). Microhooks: A novel framework to streamline the development of microservices. Computers, 14(4), 139. DOI: https://doi.org/10.3390/computers14040139.
9. Schuhknecht, F. M., Henneberg, J., May, N., & Tatbul, N. (2023). Accelerating main-memory table scans with partial virtual views. In DaMoN '23: Proceedings of the 19th International Workshop on Data Management on New Hardware (pp. 89–93). Association for Computing Machinery. DOI: https://doi.org/10.1145/3592980.3595315.
10. Kulkarni, K., & Michels, J.-E. (2012). Temporal features in SQL:2011. SIGMOD Record, 41(3), 34–43. DOI: https://doi.org/10.1145/2380776.2380786.
Downloads
Published
Issue
Section
License
Copyright (c) 2026 System technologies
This work is licensed under a Creative Commons Attribution 4.0 International License.
