How to represent a 2-D data matrix in a database

Question

I have a data set which consists of an ID and a matrix (n x n) of data related to that ID.

Both the column names (A,B,C,D) and the Row names (1,2,3) are also important and need to be held for each individual ID, as well as the data (a1,b1,c1,d1,...)

for example:

ID | A | B | C | D |

1 | a1 | b1 | c1 | d1 |

2 | ... | ... | ... | ... |

3 | ... | ... | ... | ... |

I am trying to determine the best way of modelling this data set in a database, however, it seems like something that is difficult given the flat nature of RDBMS.

Am I better off holding the ID and an XML blob representing the data matrix, or am i overlooking a simpler solution here.

Thanks.

Answer 1

can the data be thought of as "row data"? if so then maybe you could store each row as a Object (or XML Blob) with data A,B,C,D and then, in your "representation", you use something like a LinkedHashMap (assuming Java) to get the objects with an ID key.

Also, it seems that by its very basic nature, a typical database table already does what you need doesn't it?

Answer 2

RDBMSes aren't flat. The R part sees to that. What you need is:

Table Entity
------------
ID

Table EntityData
----------------
EntityID
MatrixRow (1, 2, 3...)
MatrixColumn (A, B, C, D...)
Value

Entity:EntityData is a one-to-many relationship; each cell in the matrix has an EntityData row.

Now you have a schema that can be analyzed at the SQL level, instead of just being a data dump where you have to pull and extract everything at the application level in order to find out anything about it.

Answer 3

If you want a truly relational solution:

Matrix
------
id

Matrix_Cell
-----------
matrix_id
row
col
value

But constraints to make sure you had valid data would be hideous.

I would consider a matrix as a single value as far as the DB is concerned and store it as csv:

Matrix
------
id
cols
data

Which is somewhat lighter than XML.

Answer 4

I'd probably implement it like this:

Table MatrixData
----------------
id
rowName
columnName
datapoint

If all you're looking for is storing the data, this structure will hold any size matrix and allow you to reconstitute any matrix from the ID. You will need some post-processing to present it in "matrix format", but that's what the front-end code is for.

Answer 5

This is one of the reasons why postgresql supports arrays as a data type. See

• http://www.postgresql.org/docs/8.4/static/functions-array.html and
• http://www.postgresql.org/docs/8.4/static/arrays.html

Where it shows you can use syntax like ARRAY[[1,2,3],[4,5,6],[7,8,9]] to define the values of a 3x3 matrix or integer val[3][3] to declare a column type to be a 3x3 matrix.

Of course this is not at all standard SQL and is Postgresql specific. Other databases may have similar-but-slightly-different implementations.