Easy way to observe user activity - how improve my database structure.
- by Thomas
Welcome,
I need some advise to improve perfomence my web application.
In the begin I had this structure of database:
USER
-id (Primary Key)
-name
-password
-email ....
PROFILE
-user Primary Key, Foreign Key (USER)
-birthday
-region
-photoFile ...
PAGES
-id (Primary Key)
-user Foreign Key(USER)
-page
-date
COMMENTS
-id (Primary Key)
-user Foreign Key(USER)
-page Foreign Key(PAGE)
-comment
-date
FAVOURITES_PAGES
-id (Primary Key)
-user Foreign Key(USER)
-favourite_page Foreign Key(PAGE)
-date
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.
For better perfomance (I think) I changed my structure to this:
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)
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:
ACTIVITY
id user date page comment ...
1 2 2010-02-23 None 1
2 1 2010-02-21 1 None
....
It is corect solution.
When I display about 40 records in one page (pagination) I must wait about one secound, but database is almost emty (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?
The website is built in Python/Django.
Any advices/opinion?
