title: Tracking annotations for a collection of books with a SQL Database
body: <p>I like to create a database driven web application (PHP with mySQL) that displays the collected works (sources) of several ancient and medieval philosophers. The sources should be available in their original languages, mainly Ancient Greek, Latin, and Arabic. Users should be able to translate and comment any content of sources.</p>
  
  <p>Author's <code>i</code> collected works are stored in  <code>scrAuthori</code>:</p>
  
  <pre><code> PK
  |scrAuthoriId|booktitle|page|line|position|word
  |1           |bookA    |1   |1   |1       |word1
  |2           |bookA    |1   |1   |2       |word2
  ...
  |342         |bookB    |234 |3   |11      |word3453
  
  </code></pre>
  
  <p>Author’s <code>i</code> collected works  have different kinds of content that are of interest: words, expressions covering more then two words, a sentence, sentences, a paragraph, paragraphs, etc. Users are able to define what content is of interest (i.e. BookA, page 1, line 3 to  BookA, page 3, line 5). The will translate the content and add comments to it. </p>
  
  <p>Contents are defined in <code>authoriContents</code>:</p>
  
  <pre><code> PK                FK1          FK2
  |authoriContentsId|scrAuthoriId1|scrAuthoriId2|
  |1                |1            |100
  |231              |234          |1029
  
  </code></pre>
  
  <p>Translation in <code>translationsAuthori</code>:</p>
  
  <pre><code> PK                    FK
  |translationAuthorIId|authorIContentsId|translation|
  |1                   |3                |uvw
  |2                   |3                |xyz
  |2                   |45               |abc
  </code></pre>
  
  <p>The relation between comments and content has to be many-to-many: A user’s comment pertains to two or more contents and content could have more than one comment.</p>
  
  <p><code>authorIContents_author1Comments</code>:</p>
  
  <pre><code> FK                FK
  |authoriContentsId|authoriCommentsId
  |1                |3        
  |4                |3        
  |231              |45   
  </code></pre>
  
  <p><code>authoriComments</code>:</p>
  
  <pre><code> PK                FK
  |authoriCommentsId |comment
  |3                 |comment on content 1 and 4
  |45                |comment on content 231
  </code></pre>
  
  <p>Since this is my first database application I’am not sure if the solution feasible. Is it a bad decision in light of performance to store the collected works word by word? Each <code>scrAuthori</code>, <code>i = 1, 2, ... 10</code> will have up to one million rows. Once established the rows of <code>scrAuthori</code> will not change. Is there any better approach to the problem of tracking annotations to various kind of content?</p>