Database Design for 2D Matrix Algebra

Can anyone advise on a database design/DBMS for storing 2D Time Series Matrix data. To allow for quick BACK END algebraic calculations: e.g:

Table A,B,C.. Col1: Date- Timestamp col2: Data- Array? (Matrix Data)

• SQL Psuedo Code

INSERT INTO TABLE C SELECT Multiply A.Data A by B.Data Where Matrix A Start Date = Matrix B Start Date And Matrix A End Date = Matrix B End Date

Essentially set th开发者_StackOverflow社区e co-ordinates for the calculation.

The difficulty with matrix algebra is determining what is a domain on the matrix for data modelling purposes. Is it a value? Is it a matrix as a whole? This is not a pre-defined question, so I will give you two solutions and what the tradeoffs are.

Solution 1: Value in a matrix cell is a domain:

 CREATE TABLE matrix_info (
     x_size int,
     y_size int,
     id serial not null unique,
     timestamp not null,
 );

 CREATE TABLE matrix_cell (
     matrix_id int references matrix_info(id),
     x int,
     y int,
     value numeric not null,
     primary key (matrix_id, x, y)
);

The big concern is that this does not enforce matrix sizes very well. Additionally a missing value could be used to represent 0, or might not be allowed. The idea of using a matrix as a whole as a domain has some attractiveness. In this case:

CREATE TABLE matrix (
  id serial not null unique,
  timestamp not null,
  matrix_data numeric[]
);

Note that many db's including PostgreSQL will enforce that an array is actually a matrix. Then you'd need to write your own functions for multiplication etc. I would recommend doing this in an object-relational way and on PostgreSQL since it is quite programmable for this sort of thing. Something like:

CREATE TABLE matrix(int) RETURNS matrix LANGUAGE SQL AS
$$ select * from matrix where id = $1 $$;

CREATE FUNCTION multiply(matrix, matrix) RETURNS matrix LANGUAGE plpgsql AS 
$$
DECLARE matrix1 = $1.matrix_data;
        matrix2 = $2.matrix_data;
begin
   ...
end;
$$;

Then you can call the matrix multiplication as:

SELECT * FROM multiply(matrix(1), matrix(2));

You could even insert into the table the product of two other matrices:

INSERT INTO matrix (matrix_data)
SELECT matrix_data FROM multiply(matrix(1), matrix(2));