title: Designing a database for storing tags of audio files
body: <p>I want to build a database which contains all the tags of a collection of audio
  files (FLAC, Vorbis, MP3, whatever). I've already sorted out the extraction
  (that was the easy part), but now I have some doubts about how to properly
  design the database that will contain them.</p>
  
  <p>At the moment I have normalised it like this
  as a straightforward 1:m relationship:</p>
  
  <pre><code>file: filename, size, last_modified, …
  tags: filename, tag, seq, value
  </code></pre>
  
  <p>Where <em>filename</em> is the primary key for the <code>file</code> table and <code>( filename, tag,
  seq )</code> the primary key for the <code>tag</code> table.  Some tags do appear more than once;
  the <code>seq</code> column is just a number which remembers the exact order of those.</p>
  
  <p>However, with a design like this extracting meaningful information about the
  files becomes a real pain. If I e.g. want to have just the <code>ARTIST</code>, <code>ALBUM</code> AND
  <code>TITLE</code> fields for each track I already have to join the <code>file</code> and <code>tags</code> table
  three times:</p>
  
  <pre><code>SELECT filename, artist.value, album.value, title.value
  FROM file
      LEFT OUTER JOIN tags artist USING ( filename )
      LEFT OUTER JOIN tags album USING ( filename )
      LEFT OUTER JOIN tags title USING ( filename );
  WHERE
      artist.tag = 'ARTIST'
      AND album.tag = 'ALBUM'
      AND title.tag = 'TITLE';
  </code></pre>
  
  <p>It's beyond question that this is not only extremely cumbersome to write, but
  is also quite slow because of all those joins. And this is only a simple
  example. In effect, all the queries that I eventually want to pose will piece
  together all the tags that they need as if they were stored as the columns of a
  large table.</p>
  
  <p>I've already thought about not normalising the tags and just keep them as
  columns of the <code>FILE</code> table. But the number of tags is highly variable; some of
  the more standard tags like <code>ARTIST</code> and <code>TITLE</code> are almost guaranteed to be
  present, some of the more obscure ones are only on some of the files, but I need
  to work with them too.</p>
  
  <p>To me it looks like I am trying to do it the wrong way, especially the <code>tags</code>
  table is "structured". Is there a better way to deal with this kind of data?
  For reference: I'm using PostgreSQL.</p>
  
  <p>I gather from <a href="https://stackoverflow.com/questions/1051055/how-to-store-meta-data-on-columns">this post</a> that my schema above is an <a href="http://en.wikipedia.org/wiki/Entity-Attribute-Value_model" rel="nofollow noreferrer">EAV model</a>, so it looks like I'm up for quite a hard problem…</p>