42

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.

miguel
  • 2,961
  • 4
  • 26
  • 34

6 Answers6

37

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.

chaos
  • 122,029
  • 33
  • 303
  • 309
8

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

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 val integer[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.

Yodan Tauber
  • 3,907
  • 2
  • 27
  • 48
jdkoftinoff
  • 2,391
  • 1
  • 17
  • 17
  • 2
    That's cool, but can you range index, slice, step the n-dimensional array using just SQL? Or do I have to extract the value out then work on it? – CMCDragonkai Sep 23 '15 at 08:00
4

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.

Draemon
  • 33,955
  • 16
  • 77
  • 104
  • Truly relational would mean that repeating row & column names would be stored as separate entities. You would have 'Matrix_RowNames' and 'Matrix_ColumnNames' as separate tables. – mathijsuitmegen Oct 07 '10 at 14:17
  • 2
    You mean "normalized" rather than "relational" I think. But in any case, I wasn't using names for the columns/rows and the (row,col) values are unique. – Draemon Oct 07 '10 at 15:29
2

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.

Lee
  • 18,529
  • 6
  • 58
  • 60
0

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?

djangofan
  • 28,471
  • 61
  • 196
  • 289
-1

Or even better what you can do is, create a logical array like structure. Say u want to store an m X n array.. Create m attributes in the table. In each attribute store n elements separated by delimiters ...

while retrieving the data, simply do reverse parsing to easily get back the data..

Mahesh Gupta
  • 2,688
  • 9
  • 30
  • 46