April 15, 2010

Relational database dimensions (Excel)

Because Excel allows you to enter anything in any cell, it can contain three or more dimensions of information 'smashed' into two dimensions. This can make for pretty tables, if you are going to do a statistical analysis remember that each row and each column should contain only one type of information.

EXAMPLE: I received an Excel spreadsheet of study data. The first column is variable names, the second is the first participant's day 1 data. the third is participant's day 2 data, the fourth is day 3 data. So far so good, because row 1 is always the ID, row 3 is whether juice was consumed that day, etc. Column five is the sum of the 3 days of data, so it does not match the other three.

ID#_    1   1   1   1   2   2   2   2
day_    1   2   3   -   1   2   3    -
juice   y   n   y     y   y   y   3
milk     n   n   n   0   y   y   y   3

Because it was a small data set I simply deleted the summary columns. If I had been asked earlier I would have suggested putting running totals in additional rows below the day's variables rather than adding columns with different meanings.

December 5, 2008

Relational Database dimensions (FileMaker)

E.g. Microsoft Word
One dimensionsal - just a list. You can create tables, but that is like creating multiple columns - it is still a list, just broken into columns for convenience.

E.g. Microsoft Excel
Two dimensional - rows and columns. In some sense Excel recognizes that a column belongs together and a row belongs together. For example, a row may be a name and a set of grades for that person. The first column would then be the names of students in a class, the second column their scores on Quiz 1, etc.

E.g. Microsoft Access, FileMaker
Concept 1: Three dimensional - rows, columns and pillars. (Picture to come.) Take the Excel example and go to the last column - final score. Then add a pillar coming out of each cell, representing the final grade. This pillar can take values from A to F, and slides up or down until the appropriate grade is in the underlying Excel spreadsheet.

Concept 2: Databases have spreadsheets as their underlying data. Think of that as not a flat piece of paper but as a bunch of boxes without tops, all lined up in rows and columns. The database allows you to create different views of that spreadsheet. Imagine a big piece of cardboard that covers all of the boxes. You can cut windows in it so when you set it on the boxes you only see what you choose to see.