Matrices and databases

Question

I went through the topic and found out this link quite useful and simple at the same time. http://stackoverflow.com/questions/2142566/storing-matrices-in-a-relational-database But can you please let me know if the way mentioned as

A B C D
E F G H
I J K L

[A B C D E F G H I J K L]

is the best and simple or even reliable way of storing the matrix elements in the database. Moreover I need to multiply two matrices and make the operation dynamic. So will the storage of data this create any problems for the task?

Answer 1

I think it pretty much depends on how you want to use the matrices in your application.

Is the DB only for persistence for the same application, speed is important, and sizes cannot be known in advance? Make your own serialization scheme, and save the binary blob.

Is the DB for sharing in between applications, with the size not known in advance? Use the comma delimited list.

Are you concerned with data integrity, type safety, and would like to query individual cells? Then use the (row, col, cell value) schema.

Do you know that your matrices are of fixed size and relatively small, for example 4X4 transformation matrices, and will have a 1 to 1 relationship to whatever element you have in the DB? Then you could actually have 16 rows in your table, layed out in line.

Think about your use cases, and experiment!

Answer 2

In postgresql you can actually have multidimensional arrays, define your own types and define your own functions on those types. For instance one could simply do:

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

See The PostgreSQL manual for info on how to create your own types.

Answer 3

is the best and simple or even reliable way of storing the matrix elements in the database. Moreover I need to multiply two matrices and make the operation dynamic. So will the storage of data this create any problems for the task?

I'll start by saying both approaches are valid, but the second one is not sufficient as written by you. You have to have some other information, like the length of the rows or the (row, col) indexes of each element to store a matrix as a 1D array. This is commonly done for sparse matricies, where there are lots of zeros surrounding values clustered on either side of the diagonal.

Persisting the matrix in a database and operating on it in memory are two separate things.

Tasks like multiplying require (row, col) indexes. Storing the matrix as a 2D array means that you'll have them, so no other info is needed. The 1D array needs this info too, so you'll have to supply it.

The advantage swings to the 1D array for sparse matricies. You don't have to store zero values outside the bandwidth in that case, but your operations like addition and multiplication become more complex to code.