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
