By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

three dimensions in a table

P: 30
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.
May 19 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
May 19 '08 #2

P: 30
I surely will, thank you so far !
May 19 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
May 19 '08 #4

P: 30
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.
May 19 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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-
    Expand|Select|Wrap|Line Numbers
    1. dim1(long) 4 bytes
    2. dim2(long) 4 bytes
    3. dim3(long) 4 bytes
    4. value(boolean) 1 byte (or maybe 2 bytes)
    5. =14 bytes
    6. + overhead (mainly indexes I guess)
    7.  
    let us give 50 full bytes for the whole record
give somewhat about 4e7 records before you reach 2Gb limit

Regards,
Fish
May 20 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.