title: Designing Database Schema for Event-based Analytics
body: <p>I'm trying to figure out the best way to model the schema for this event-based analytics system I'm writing. My main concern is writing this in a way that makes queries simple and fast. I'm going to be using MySQL as well. I'll go over some of the requirements and present an outline of a possible (but I think poor) schema. </p>
  
  <h1>Requirements</h1>
  
  <ul>
  <li><p>Track events (e.g. track occurrences of the "APP_LAUNCH" event)</p></li>
  <li><p>Define custom events</p></li>
  <li><p>Ability to segment events on >1 custom properties (e.g. get occurrences of "APP_LAUNCH" segmented on the "APP_VERSION" property) </p></li>
  <li><p>Track sessions</p></li>
  <li><p>Perform queries based on timestamp range</p></li>
  </ul>
  
  <h1>Possible Modeling</h1>
  
  <p>The main problem that I'm having is how to model segmentation and the queries to perform to get the overall counts of an event. </p>
  
  <p>My original idea was to define an EVENTS table with an id, int count, timestamp, property (?), and a foreign key to an EVENTTYPE. An EVENTTYPE has an id, name, and additional information belonging to a generic event type. </p>
  
  <p>For example, the "APP_LAUNCH" event would have an entry in the EVENTS table with unique id, count representing the number of times the event happened, the timestamp (unsure about what this is stamped on), and a property or list of properties (e.g. "APP_VERSION", "COUNTRY", etc.) and a foreign key to an EVENTTYPE with name "APP_LAUNCH". </p>
  
  <h1>Comments and Questions</h1>
  
  <p>I'm pretty sure this isn't a good way to model this for the following reasons. It makes it difficult to do timestamp ranged queries ("Number of APP_LAUNCHES between time x and y"). The EVENTTYPE table doesn't really serve a purpose. Finally, I'm unsure as to how I would even perform queries for different segmentations. The last one is the one I'm most worried about. </p>
  
  <p>I would appreciate any help in helping to correctly model this or in pointing me to resources that would help. </p>
  
  <p>A final question (which is probably dumb): Is it bad to insert a row for every event? For example, say my client-side library makes the following call to my API: </p>
  
  <pre><code>track("APP_LAUNCH", {count: 4, segmentation: {"APP_VERSION": 1.0}})
  </code></pre>
  
  <p>How would I actually store this in the table (this is closely related to the schema design obviously)? Is it bad to simply insert a row for each one of these calls, of which there may be a significant amount? My gut reaction is that I'm really interested mainly in the overall aggregated counts. I don't have enough experience with SQL to know how these queries perform over possibly hundreds of thousands of these entries. Would an aggregate table or a in-memory cache help to alleviate problems when I want the client to actually get the analytics? </p>
  
  <p>I realize there are lots of questions here, but I would really appreciate any and all help. Thanks! </p>