Designing a table in Access database

[Guest]

I need to create a table in Access database for storing daily and hourly
information on response rates per 399 transaction types.

Option 1 - This table would have 399 (J type transactions) x 24 (number of
hours per day) x 40 bytes (field size) = 383,040 bytes.
TRAN HH EX COUNT IO COUNT TOT RESP
J001 00 999999999 999999999 999999999
J001 01 999999999 999999999 999999999
J001 02 999999999 999999999 999999999
J002 00 999999999 999999999 999999999
J002 01 999999999 999999999 999999999
J002 03 999999999 999999999 999999999

Option 2 - Jamming all 24 hours into one record = 399 (J type transactions)
x 800 bytes (field size) = 319,200 bytes
TRAN HH EX COUNT IO COUNT TOT RESP HH EX COUNT IO COUNT TOT RESP
J001 00 999999999 999999999 999999999 01 999999999 999999999 999999999

For reporting purposes, Option 2 seems more appropriate. However, for
future data trending and statistical reporting, I am leaning more towards
Option 1.

I would like to get feedback from experts on which option is better.

 

[Douglas J. Steele]

Option 2 would actually violate database normalization principles.

Option 1 is definitely the correct approach.

 

[Guest]

Thank you for your response. I am obviously a newbie and just wanted
confirmation that I am heading in the right direction. Have a good day!

 

[John Vinson]

For reporting purposes, Option 2 seems more appropriate. However, for
future data trending and statistical reporting, I am leaning more towards
Option 1.

I would like to get feedback from experts on which option is better.

Option 1, no question at all.

One reason (among many): what happens when the Powers That Be decide
that they want every fifteen minutes instead of every hour? Redesign
all your tables, queries, forms, and reports?

Note that you can use a Crosstab query to generate the wide-flat view
for reporting purposes, if that's desirable - *even if you go to finer
granularity*.

John W. Vinson[MVP]

 

[Guest]

Understood and thanks for your response.

Here is a follow-up question on same example:
I will need to calculate the Average Response Time per hour. Formula would
be TOT RESP/EX COUNT.

Several folks have indicated in prior postings that calculated data should
not be stored in a table field. Is there an exception to that knowing that I
would be calculating at least 399X24 = 9576 times per day? (I am not sure
how slow/fast this can be processed).

If the Average Response Time can be calculated from another system, would it
make more sense to have it calculated there and included as another field in
the data that I will be importing daily?

 

[John Vinson]

If the Average Response Time can be calculated from another system, would it
make more sense to have it calculated there and included as another field in
the data that I will be importing daily?

Only if a) you have a DEMONSTRATED - not hypothetical - performance
problem and b) can be absolutely certain that none of the data will
ever need to be corrected (possibly invalidating the calculation).

John W. Vinson[MVP]