Journal of Technology and Informatics
Relational Database Model Design for Logistics and Shipment Tracking Systems
DOI:
https://doi.org/10.37802/Keywords:
Relational Database, Shipment Tracking, Entity Relationship Diagram, SQL, GPS PingAbstract
Efficient and reliable shipment tracking is essential for modern logistics, where managing large volumes of real-time transportation data remains a significant challenge. This study proposes a normalized relational database model to support structured logistics data management and shipment tracking using Entity Relationship Diagrams (ERDs) and Structured Query Language (SQL). The methodology involved analyzing a Transportation and Logistics dataset comprising 6,880 records and 32 attributes, identifying entity relationships, and applying normalization up to the Third Normal Form (3NF). The resulting database was implemented in Microsoft SQL Server and evaluated using simulated datasets containing 10,000, 100,000, and 1 million records. Experimental results demonstrated that complex queries could be executed in approximately 6 seconds for 1 million records. Furthermore, integrating a dedicated GPS_Ping table provides a structured mechanism for storing time-stamped shipment location observations, supporting location-based tracking data management. The proposed model reduces data redundancy, strengthens relational data integrity, and supports scalable query processing. These findings demonstrate the potential of a normalized relational database architecture as a foundation for developing scalable logistics information systems and reliable shipment tracking applications
Downloads
References
[1] D. G. Dong, "Design and development of intelligent logistics tracking system based on computer algorithm," Int. J. Appl. Inf. Manag., vol. 3, no. 2, pp. 58–69, 2023, doi: 10.47738/ijaim.v3i2.56.
[2] W. Gunawan, M. Listya, H. Alianto, and D. A. Nugroho, "Real time tracking system integrated with IoT for package delivery in Indonesia's logistic company with TOGAF implementation," in Proc. IEEE ICISS, 2023, doi: 10.1109/ICISS59129.2023.10291211.
[3] Z. Z. Li, J. Zhao, H. Cui, E. X. Zheng, X. Ma, D. Y. W. Liu, and M. H. Au, "Blockchain for smart logistics: Enhancing identity security, bidding transparency and goods tracking," in Proc. 6th ACM Int. Symp. Blockchain and Secure Critical Infrastructure (BSCI), 2024, pp. 1–9, doi: 10.1145/3659463.3660028.
[4] D. M. Kroenke, D. J. Auer, R. C. Yoder, and S. L. Vandenberg, Database Processing: Fundamentals, Design, and Implementation, 16th ed. Pearson Education, 2024. [Online]. Available: https://studentebookhub.com/wp-content/uploads/2024/preview/9780136930174.pdf
[5] J. T. Haryanto, "Sistem logistik berbasis web untuk usaha mikro, kecil, dan menengah," J. Edukasi Teknol. Inf. (EDUTIK), 2022. [Online]. Available: https://www.researchgate.net/publication/ 373377084_Sistem_Logistik_Berbasis_Web_untuk_Usaha_Mikro_Kecil_dan_Menengah
[6] R. Elmasri and S. B. Navathe, Fundamentals of Database Systems, 7th ed. Pearson Education, 2016. [Online]. Available: https://www.auhd.edu.ye/upfiles/elibrary/Azal2020-01-22-12-28-11-76901.pdf
[7] D. Effendi, "Perancangan sistem pelacakan pengiriman barang (tracking system for freight forwarding) berbasis SMS (short message service)," Wawasan Tridharma, vol. 11, pp. 1–10, 2017. [Online]. Available: https://www.researchgate.net/publication/ 320099494_Perancangan_Sistem_Pelacakan_Pengiriman_Barang_Tracking_System_ For_Freight_Forwading_Berbasis_SMS_Short_ Message_Service
[8] A. Zulfikar and T. Hidayat, "Analisis dan perancangan database menggunakan model konseptual data warehouse pada toko online," J. EDUKATIF: J. Ilmu Pendidikan, vol. 3, no. 6, pp. 4622–4630, 2021. [Online]. Available: https://edukatif.org/edukatif/article/download/1477/pdf
[9] Y. Y. Joefrie and P. P. Kalatiku, "Desain basis data sistem informasi akademik di Fakultas Teknik Universitas Tadulako," J. Ilm. Foristek, vol. 2, pp. 190–194, 2013. [Online]. Available: https://foristek.fatek.untad.ac.id/index.php/foristek/article/view/101/76
[10] Y. Jing and P. Yang, "Research on the risk prevention of cross-border e-commerce logistics in China by applying big data technology," Procedia Comput. Sci., vol. 208, pp. 407–413, 2022. [Online]. Available: Research on the Risk Prevention of Cross-Border E-Commerce Logistics in China by Applying Big Data Technology - ScienceDirect
[11] R. A. Mohammed, A. Nadi, L. Tavasszy, and M. de Bok, "Data fusion approach to identify distribution chain segments in freight shipment databases," Transp. Res. Rec., vol. 2677, no. 6, pp. 310–323, 2023. [Online]. Available: Data Fusion Approach to Identify Distribution Chain Segments in Freight Shipment Databases - Raeed Ali Mohammed, Ali Nadi, Lórant Tavasszy, Michiel de Bok, 2023
[12] B. R. Prakash, K. V. Nagaraja, and H. P. M. Kumar, "Streaming analytics for shipment monitoring system," in Proc. Int. Conf. Futuristic Technol. (INCOFT), Belgaum, India, 2022, pp. 1–4. [Online]. Available: Streaming Analytics for Shipment Monitoring System | IEEE Conference Publication | IEEE Xplore
[13] G. Barlian and S. Susanti, "Sistem informasi tracking logistik berbasis website pada distribution center PT Akur Pratama (YOGYA GROUP)," INTEK: J. Inform. Teknol. Inf., vol. 8, no. 1, pp. 13–20, 2024. [Online]. Available: https://jurnal.umpwr.ac.id/index.php/intek/article/view/1605
[14] M. F. A. Prasetyo, R. Widodo, and R. Maulana, "Rancang bangun tracking pengiriman berbasis website menggunakan metode systems development life cycle (SDLC) dengan model waterfall," J. Teknol. Inf. Komun. (JTIK), vol. 8, no. 2, pp. 115–123, 2024. [Online]. Available: https://journal.lembagakita.org/jtik/article/view/1387
[15] R. J. Pratama, "Perancangan sistem informasi laporan pengiriman barang berbasis web di inventory PT. Setiap Hari Dipakai," J. Indones. Sos. Teknol., vol. 2, no. 11, pp. 1719–1728, 2021. [Online]. Available: https://jist.publikasiindonesia.id/index.php/jist/article/view/281
[16] L. Rosiana and I. Yuadi, “K-Means Clustering untuk Analisis Tren Peminjaman Buku di Perpustakaan,” J. Technol. Inform. (JoTI), vol. 7, no. 1, pp. 1–10, Apr. 2025, doi: 10.37802/joti.v7i1.933.
[17] J. Anggara et al., “Pengembangan Sistem Prediksi Harga dan Rekomendasi Mobil Bekas Berbasis Machine Learning,” J. Technol. Inform. (JoTI), vol. 7, no. 1, pp. 11–23, Apr. 2025, doi: 10.37802/joti.v7i1.987.
[18] P. Pavithra and S. Savitha, “Topic Modeling for Evolving Textual Data Using LDA, HDP, NMF, BERTopic and DTM With a Focus on Research Papers,” J. Technol. Inform. (JoTI), vol. 5, no. 2, pp. 53–63, Apr. 2024, doi: 10.37802/joti.v5i2.618.
[19] N. Machado, "Transportation and Logistics Tracking Dataset," Kaggle. [Online]. Available: https://www.kaggle.com/datasets/nicolemachado/transportation-and-logistics-tracking-dataset
[20] R. Steffan and H. Mumm, "Database design for vehicle routing problems," in Perspectives in Business Informatics Research, Local Proceedings, Riga, Latvia, pp. 12–20, 2011. [Online]. Available: https://www.researchgate.net/publication/331398807_Database_Design_for_ Vehicle_Routing_Problems
[21] Q. Li and G. Wu, "ERP system in the logistics information management system of supply chain enterprises," Mobile Information Systems, vol. 2021, Art. no. 7423717, pp. 1–11, 2021, doi: 10.1155/2021/7423717. [Online]. Available: https://onlinelibrary.wiley.com/doi/10.1155/2021/7423717
