Re: Best table structure
Hello Tom, Wednesday, June 30, 2004, 1:26:52 PM, you wrote: TC My question is this: TC Would I be better keeping this format and inserting multiple rows on each TC submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x TC Artists, 20 x Titles, 20 x Labels.) TC I know that the latter will be easier to query. Table design aside - you need to ask yourself this: How often will this multiple insert be occurring? Sure, you are performing 20 inserts one after the other, but if you only do this once an hour then it really isn't as much of an issue as you think I don't believe. Try and balance out the admin / code headache that would be an 81 column table vs. exactly how often this is even an issue anyway. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services I am not young enough to know everything. - Oscar Wilde -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best table structure
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.ukcc:
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]
RE: Best table structure
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]
