The current approach is better. Having one row with 81 columns will be harder to deal with in terms of writing code to display it. The size of the table will be roughly the same either way -- either you have a few very big rows, or many small rows, but that shouldn't be a huge issue if you index things properly.
You may want to introduce some way to let you cull old data automatically. For instance, perhaps add a TIMESTAMP column and delete anything over 30 days old. Or possibly an AUTO_INCREMENT column, and delete anything below MAX(id) - (N * 20) to ensure only N top-20 lists are in the table. -JF > -----Original Message----- > From: Tom Chubb [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 30, 2004 5:27 AM > To: [EMAIL PROTECTED] > Subject: Best table structure > > 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]
