database - Storing occurrences for reporting -

what best way store occurrences of event in database can pull reports on it? ie (total number of occurrences, number of occurrences between date range).

right have 2 database tables, 1 holds individual timestamps of event - can query on date range, , 1 holds total count can pull number tally

table 1:

event | total_count ------+------------ bar   |  1 foo   |  3 

table 2:

event | timestamp ------+---------- bar   | 1/1/2010 foo   | 1/1/2010 foo   | 1/2/2010 foo   | 1/2/2010 

is there better approach problem? i'm thinking of converting table 2, hold date tallies, should more efficient, since date range queries done on whole dates, not timestamp (1/1/2010 vs 1/1/2010 00:01:12) ie:

updated table 2

event |   date   | total_count ------+----------+------------ bar   | 1/1/2010 |  1 foo   | 1/1/2010 |  1 foo   | 1/2/2010 |  2 

perhaps theres smarter way tackle problem? ideas?

your approach seems good. see table 2 more detail table, while table 1 summary table. part, doing inserts table 2, , inserts , updates on table 1.

the updated table 2 may not give additional benefit. however, should consider if aggregations day important you.

you may consider adding more attributes (columns) tables. example, add first_date, , last date table 1.