You'd probably be better off having rank and votes fields in the actual
album_detail table. Whether you choose to maintain the individual votes in
the album_ranks is another story. Let's say you rank an album from 1 to
10 - the rank would then be calculated like this:
$uservote = 8.5 // this is what the user voted on the web form
set rank=(votes*rank+$uservote)/(votes+1), votes=votes+1
Say your first vote was 8.5. The rank would be 8.5.
Here's how three more votes would work
(user ranks 3.5): rank=(8.5*1+3.5)/2 = 6, votes=2
(user ranks 6) rank = (6*2 + 6)/3 = 6
(user ranks 10) rank=(6*3+10)/4 = 7
That way your queries would be very fast, but you wouldn't be able to let
people change their votes unless you maitain the votes table and reverse the
above math. I'd probably drop the album_ranks table entirely.
----- Original Message -----
From: "Adam W" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, March 03, 2001 11:17 AM
Subject: Index / Rank in table
> Hi
>
> I'm designing a site where people can vote for their favourite albums
using
> PHP3 to query the database.
> It's the first database I've 'designed', and I'm sure that there are
> improvements I could make to the way I have organised the data.
>
> For example, I'm looking for a way to make the 'album_ranks' table
> superfluous... e.g. when I query the database and return the 'album_id',
> 'artist', 'title' and 'year' fields from the 'album_details' table, I
would
> like to be able to automatically return a 'rank' based on the 'album_id'
> position in the 'album_votes' table. Is it possible to do this? Currently,
> everytime an album is voted for, and the 'votes' column is updated in
> 'album_votes', I am repopulating the whole of the 'album_ranks' table on
the
> basis of the updated vote... considering that this table contains in
excess
> of 20,000 elements, this seems to me to be extremely inefficient.
>
> I have read what I can on 'indexes' (I'm sure that the solution lies there
> somewhere), but I'm still in a fog of ignorance on the subject. Could
anyone
> point me in the right direction????
>
> Yours gratefully
>
> Adam
>
> [ example structure below... ]
>
>
> SELECT * FROM album_details
>
> -----------------------------------------------
> album_id | artist | title | year
> -----------------------------------------------
> 5 | the beatles | revolver | 1966
> 17 | nirvana | nevermind | 1991
>
> etc etc...
>
>
> SELECT * FROM album_votes
>
> -----------------------------------------------
> album_id | votes
> -----------------------------------------------
> 5 | 16382
> 17 | 4732
>
> etc etc...
>
> SELECT * FROM album_ranks
>
> -----------------------------------------------
> album_id | rank
> -----------------------------------------------
> 5 | 1
> 17 | 2
>
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
