Building a database to store matrices in SQLite

Question

In a database for an engineering simulation forecasting application:

• Each user creates a "project".
• In a project, the user specifies the number of items for 2 categories: cat_1 and cat_2.
• cat_1 and cat_2 will have n1 and n2 items respectively.
• The program then converts cat_1 and cat_2 into two matrices mat_1 mat_2 such that for each matrix rows = columns = n1 or n2. I.e. if a category has 3 records ('a', 'b', and 'c'), the matrix will be 3 by 3 with the rows and columns being 'a', 'b' and 'c'.
• The matrix will be then multiplied by a factor (K_r).
• The multiplication returns 48 matrices, 96 matrices, 48 for cat_1 and 48 for cat_2.
• In each matrix there are control array/variables mat_1_aij and mat_2_bi2j2 that the user specifies and provides values for.
• These variables represent field collected data that the simulation should match.
• An algorithm iterates through both matrices a number of times (1,000 or so) until the observed value(s) == mat1_aij and mat_2bi2j2 respectively.

Question

What is the best practice way to design/build/store such a database (especially the matrices) and what are some of the design/implementation issues you might foresee?

Answer 1

I think that matrices are usually represented as adjacency lists in the relational model. That means that you have one column for every dimension of the matrix containing the coordinates and one (or multiple) columns for the value in that cell.

This should allow efficient querying but you should avoid iterating through the matrix using point-queries (i.e., requesting the value in a single cell). If possible, you should encode as much of your algorithm in (ansi/cursor-free) sql and have the DBMS execute it.

If that is not possible you would end up reading the whole matrix (or the needed chunks) from the database, perform the algorithm and write it back. If you come to this point you might want to ask yourself if a relational database is what you need.