title: Database design for complex music analytics
body: <p>I'm a researcher studying animal behavior, and I'm trying to figure out the best way to structure my data. I present short musical tunes to animals and record their responses.</p>
  
  <p><strong>The Data</strong></p>
  
  <p>Each tune consists of 1-10 notes randomly chosen from major + minor scales spanning several octaves. Each note is played for a fixed duration but played randomly within some short time window.</p>
  
  <p>I then record the animal's binary response to the tune (like / dislike).</p>
  
  <p>I play >500 tunes to the animal each day, for >300 days. I also combine data from >10 animals.</p>
  
  <p>I also need to store variables such as trial number on each day (was it the 1st tune presented? last? etc.), and date so that I know what data points to exclude due to external issues (e.g. animal stopped responding after 100 trials or for the entire day).</p>
  
  <p><strong>The Analysis</strong></p>
  
  <p>I'm trying to uncover what sorts of musical structure in these randomly generated tunes will lead to likes/dislikes from the animal. I do this in a mostly hypothesis-driven manner, based on previous research. The queries I need to perform on my dataset are of the form: "does having more notes from the same octave increase likeability of the tune?"</p>
  
  <p>I'm also performing analysis on the dataset throughout the year as data is being accumulated.</p>
  
  <p><strong>What I've tried</strong></p>
  
  <p>I combine data from all animals into a single gigantic list containing dicts. Each dict represents a single trial and its associated:</p>
  
  <ul>
  <li>animal ID#</li>
  <li>session ID#</li>
  <li>trial ID#</li>
  <li>binary response (like/dislike)</li>
  <li>tune, which is defined by a dict. The keys are simply the notes played, and the values denote when the note is played. E.g. <code>{'1A#':[30,100]}</code> means a tune with just a single note, A# from 1st octave, played from 30ms to 100ms.</li>
  </ul>
  
  <p>I save this to a single pickle file. Every day after all the animals are done, I update the pickle file. I run my data analysis roughly once per week by loading the updated pickle file.</p>
  
  <p>I've been looking to re-structure my data into a database or Pandas DataFrame format because of speed of 1) serializing data and 2) querying, and 3) possible cleaner code instead of dealing with nested dicts. I initially thought that my data would naturally lend itself well to some table structure because of the trial-by-trial structure of my experiment. Unfortunately, the definition of tunes within the table seems tricky, as the tunes don't really have some fixed structure.</p>
  
  <p>What would be possible alternatives in structuring my data?  </p>