If I was, for example, going to count "activities" across many computers and show a rollup of that activity, what would the database look like to store the data?
Simply this? Seems too simple. I'm overthinking this.
ACTIVITYID COUNT
---------- -----
Yes, I'm afraid it's that simple, assuming you are only interested in the number of times each activity occurs. Once you have that table populated, you could easily create, for example, a histogram of the results by sorting on count and plotting.
I think you could add a DateTime field so that you can do reports of the events in between a certain time interval, or at least know when the last activity count was taken.
If the volume is not going to be ridiculously large, I'd probably create a table that logs each event individually, with a DateTime as @Turnkey suggests, and possibly the machine that logged it, etc.
LOGID (PK) ACTIVITYID SOURCE DATELOGGED
---------- ---------- ------ ----------
That would give you the ability to run a query to get the current count, and also to use the data to determine events in a time period, and/or coming from a specific machine. A clustered index on ActivityID should give you good query performance, and the table is narrow so inserts shouldn't be too costly.
I think that the actual activity would create some type of record with at least an ActivityId and ActivityDate in a logging table. An other column might be the identifier of the computer creating the log entry.
You would then create the count by aggregating the activity records over a specified time period.
Metro.