Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 932
miguel Avatar asked Jul 16 '09 16:07

miguel


People also ask

What is a matrix in database?

Whereas an array is merely a data structure who elements are accessed by a numeric value called an index, a matrix is an array with mathematical operations defined on it. A matrix can be one, two, three or more dimensional structures.

Can you store a 3 dimensional table in a relational database?

Three-Dimensional Relational Table. The relational database example can be extended by adding a third dimension to the data set.


1 Answers

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.

like image 162
chaos Avatar answered Oct 04 '22 14:10

chaos