Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What would a Database Diagram (ER Diagram/Table Layout) look like for measuring distribution of something?

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
----------  -----
like image 848
Scott Hanselman Avatar asked Sep 23 '08 00:09

Scott Hanselman


2 Answers

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.

like image 85
Guy Starbuck Avatar answered Oct 04 '22 13:10

Guy Starbuck


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.

like image 35
Metro Avatar answered Oct 04 '22 14:10

Metro