Tom,
I would keep your current fiveish-column design with the 20 inserts. It
will save you a LOT of headache in the future when you want to search for
things like particular artists or titles or to compute a sort of "most
popular top twenty" based on everyone else's rankings.
SELECT artist, title, SUM(25-chartpos)
FROM chart
GROUP BY artist, title
ORDER BY SUM(25-chartpos)
LIMIT 20
That statement would have been a coding nightmare to write if you stored a
chart in 81 columns in just one row. (I used 25 so that the index scores
run from 5 to 24. The highest total score _should_ represents a song that
is listed frequently and near the tops of the charts. There are MANY other
ways to index your songs to generate a composite ranking like this. You
will have to experiment to determine what works best for you. Of course,
spelling and naming differences will affect the outcome unless you took
pains to standardize those...)
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Tom Chubb"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
o.uk> cc:
Fax to:
06/30/2004 08:26 Subject: Best table structure
AM
Please can someone let me know their opinion on the following.
I am new to MySQL and can't seem to find the right info anywhere.
I have written some code for submitting a top 20 music chart online.
I use the following to insert into mysql:
INSERT INTO chart (name, chartpos, artist, title, label) VALUES
('$name', '1', '$artist', '$title', '$label'),
('$name', '2', '$artist2', '$title2', '$label2'),
('$name', '3', '$artist3', '$title3', '$label3'),
-------------- repeat til -----------------
('$name', '20', '$artist20', '$title20', '$label20'),
Another page queries the table and sorts by name (multiple people submit
charts) and latest date.
My question is this:
Would I be better keeping this format and inserting multiple rows on each
submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x
Artists, 20 x Titles, 20 x Labels.)
I know that the latter will be easier to query.
Also, without maintenance, the size of the table for the current method
will
get extremely large. Will that affect server performance?
I am still a newbie, so plain explanations would be most appreciated.
Thanks very much in advance.
Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
