three dimensions in a table

[zaankanter]

Generaly a table wil order data in a two dimensional way, as rows of data in a predefined number of columns. Re-thinking an already tackled problem (but not so very-well-tackled: thus the re-thinking) it struck me that the real problem to overcome were the three dimensions of my problem. Let me elaborate:

Simplified the task would be: populate a database with data concerning people at all the various dates in a year. Do this for a not predefined (i.e. infinit) number of people and a not predefined (i.e. infinit) number of years.
Since every year has 365/366 days, the days could be the (predefined) nummer of columns in a table. Then the rows could be for the (not predefined) number of people. But what to do for the succesion of years? One would like to stack day/people-tables one over the other, just as long as needed (the third dimension).
Acces -of course- will not do this.

Who has brilliant thoughts about this.

[FishVal]

Hello, zaankanter.

You totally miss the very idea of relational database. I strongly recommend you to read Database Normalization and Table structures.
Relational database could easily hold N-dimensional data cube.

Regards,
Fish.

[zaankanter]

I surely will, thank you so far !

[missinglinq]

Fish's advice is spot on! Also note that tables are limited to 255 columns. Recordsets, which forms and reports are based on, are also limted to 255 fields.

Welcome to Bytes!

Linq ;0)>

[zaankanter]

I've read the recommended article.

It seems to me that what the normalisation actualy does, is to hold the "stacked" tables (as in my first posting) by a process of repeating, in just one table, like this:
dayID, peopleID, thing-to-store, wich would give something like:

1, 1, yes
1, 2, yes
1, 3, no
1, 4, yes
2, 1, yes
2, 2, no
2, 3, no
2, 4, yes
3, 1 etc etc

I can understand that this is the way a relational database should be organised.
At the same time however, this leads to an exponentional growth of records, with each foreign key in the table. In the problem I defined in my posting you see that the number of records (people*days) could easily grow into the tens of thousands (lets say the people represented in the table are some hundreds, over some ten years). I don't think acces will do this either.

[FishVal]

As usual there are several ways to overcome it.

• Other backend - MSSQL, MySQL and so on
• Database may be splitted into several mdb files. One master that holds cube dimensions tables and several slaves holding volumes of cube data table.
• BTW if cube points are du-state (yes/no as in your example), then yes-state may be related to existance of a record and no-state its absense. So number of records may be significantly reduced.
• Simple calculations-
  

  dim1(long) 4 bytes
  dim2(long) 4 bytes
  dim3(long) 4 bytes
  value(boolean) 1 byte (or maybe 2 bytes)
  =14 bytes
  + overhead (mainly indexes I guess)

  let us give 50 full bytes for the whole record

give somewhat about 4e7 records before you reach 2Gb limit

Regards,
Fish