New Result Database for the Automotive Industry using Dynamic Columns

Abstract: Scania is undergoing a transformation from being a supplier of trucks, buses and engines to a supplier of complete and sustainable transport solutions. Scania Test Rig Development (NLR) group is responsible for developing technical solutions for component testing at Scania R&D and its subdivision Testing Systems (NLRI) is responsible for the development of the software. NLRI maintains the physical server storage including a test result database where results from all over R&D are stored. This database is based on a combination of a file server and a SQL server. The database structure is outdated and restricting to users. The technology for storing data has excelled in many areas since this system was developed. This thesis proposed a new database solution consisting of a new relational model and storage improvements. The varying attributes created by the testing rigs was to be handled with dynamic columns. Dynamic columns allow one to store different sets of columns for each row in a database table. This was tried out in a prototype database using the MariaDB database management system utilizing the JSON syntax for dynamic columns with sample data converted from the database currently in use. The prototype was successfully set up and could utilize the functionalities of dynamic columns. Performance tests, or benchmarks, comparing the current database, the dynamic column prototype and a reference regular SQL column prototype were done. The tests showed that temporarily parsing a dynamic column into regular columns when querying, something referred to as creating virtual columns, was very slow in comparison to a equivalent query on the current database. When fetching the whole or parts of the dynamic columns JSON content the query speeds were only insignificantly slower than a query fetching the same data from regular SQL columns: Both were much faster than the current database. The conclusion was that the new database model with dynamic columns, along with other improvements to the structure presented in this thesis, could be a more future-compatible replacement to the current solution, although it doesn't contribute on its own to new functionalities that for example a cloud platform or advanced analysis tools could give. The proposed database system could be a good complement to further innovations.