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
---------- -----
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With