title: Easy way to observe user activity - how improve my database structure
body: <p>Welcome,</p>
  <p>I need some advise to improve perfomence my web application.</p>
  <p>In the begin I had this structure of database:</p>
  <blockquote>
  <p>USER</p>
  <p>-id (Primary Key)</p>
  <p>-name</p>
  <p>-password</p>
  <p>-email ....</p>
  <p>PROFILE</p>
  <p>-user Primary Key, Foreign Key (USER)</p>
  <p>-birthday</p>
  <p>-region</p>
  <p>-photoFile ...</p>
  <p>PAGES</p>
  <p>-id (Primary Key)</p>
  <p>-user Foreign Key(USER)</p>
  <p>-page</p>
  <p>-date</p>
  <p>COMMENTS</p>
  <p>-id (Primary Key)</p>
  <p>-user Foreign Key(USER)</p>
  <p>-page Foreign Key(PAGE)</p>
  <p>-comment</p>
  <p>-date</p>
  <p>FAVOURITES_PAGES</p>
  <p>-id (Primary Key)</p>
  <p>-user Foreign Key(USER)</p>
  <p>-favourite_page Foreign Key(PAGE)</p>
  <p>-date</p>
  </blockquote>
  <p>but now one of the most important page of website is observatory, when everyone can observe activity others users.
  So I need select all pages, comments and favourites pages some users and display it in one list, sorted by date.</p>
  <p>For better perfomance (I think) I changed my structure to this:</p>
  <pre><code>table USER and PROFILE without changes
  
  ACTIVITY (additional table- have common fields: user,date) 
  -id (Primary Key)
  -user Foreign Key(USER)
  -date
  -page Foreign Key(PAGE)
  -comment Foreign Key(COMMENTS)
  -favourite_page Foreign Key(FAVOURITES_PAGES)
  
  
  PAGES
  -id (Primary Key)
  -page
  
  COMMENTS
  -id (Primary Key)
  -page Foreign Key(PAGE)
  -comment
  
  FAVOURITES_PAGES
  -id (Primary Key)
  -favourite_page Foreign Key(PAGE)
  </code></pre>
  <p>So now it is very easy get sorted records from all tables.
  But I have no only foreign key to PAGES, COMMENTS and FAVOURITES_PAGES in ACTIVITY table - there is about ten Foreign Key fields and in one record only one have value, others have None:</p>
  <pre><code>ACTIVITY
  id    user     date page comment ...
  1    2 2010-02-23 None     1
  2    1 2010-02-21 1         None
  ....
  </code></pre>
  <p>It is corect solution.
  When I display about 40 records in one page (pagination) I must wait about one secound, but database is almost empty (a few users and about 100 records in others tables). It is depends on amount records per page - I have checked it, but why it takes too long time, becouse of relationships?</p>
  <p>The website is built in Python/Django.</p>
  <p>Any advices/opinion?</p>