Database design & table relationships: where would the data go? (Image included).

Question

I need to carryout a data capture exercise, which is looking like a large task, that unfortunately may end up being done in excel. I believe a database is more suitable, but the structure of it is probably very complicated.

I've created 4 categories per Unit (30 units). Each category has 8 graphs/dimensions. Each graph/dimension has a scale that I've visually broken down into 4 major interval points (inerval1, Interval2, etc). I'm intending to put a figure in a box that represents the change against these 4 interval points. Therefore, 4(categories)*8(dimensions) = 32. Then 32*4(intervals) = 128. This means per unit I need to record 128 changes.

...and the best thing, there are 3 distincy scales. 4 of the graphs use one scale. 2 use another and the last 2 use a final one.

Like I said this is a monster of a task and doing this in excel is possible, but doesn't give me the flexibility I think I need when it comes to comparing the data.

• 30 Units (tblInventory)
• 4 Categories per Unit (tblCategories1, tbleCategories2, etc)
• 8 Dimensions/Grpahs per categroy. (Dim1, Dim2, etc)
• 3 Scales (tblScale1, tblScale2, etc)

I'm trying to figure out where the actual data would be captured. Would I have a single table called tblIntervalData that is related to a linking table that connects to each of the 3 tblScales, which in turn are linked to the tblDimensions?

Below is a screen grab of what I've done, but it doesn't feel right. Your views and advice will be much appreciated. '

A higher resolution image can bee seen here

Many thanks

Mike.

Answer 1

I can't see your pic behind my stupid corporate firewall, but...

A). Since excel only really manages to handle two (arguably three) dimensions of data at all well it's very unlikely that not going the DB route is correct if you have any kind of relations to deal with.

B). Stop usign hungarian notation, by which I mean drop the "tbl" prefixes.

C). I'd agree that sort of sounds like you do want a table (or similar table*s*) "Intervals" (avoid the word data - everything is data) which will have FK relationships to Units, Scales, etc, but it's hard for me to be sure without seeing your diagram I think. Limited help I know.