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]
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.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]
R: Best table structure
> 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. Are you sure? Why? > Also, without maintenance, the size of the table for the current > method will > get extremely large. Will that affect server performance? There is no difference: the size is the same for the 2 tables (actually the current one has one tinyint more which is also part of an index, but that is almost nothing). The current method (IMHO) is much better. What if you want to perform a search for artist in the other case? What if you want to change the design to top 10 instead of top 20? And so on... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
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]
Re: Seeking advice on best table structure
* Rajesh Kumar > Roger Baklund unknowingly asked us: > > >>What would be a good way to deal with the following... No, I did not. Scott Haneda asked the question. I replied. :) And I recommended considering the SET column. > But for a couple of reasons, SET is not recommend. > > 1. It introduces fragmentation, and indexes are pretty hard. Fragmentation? > 2. It defies the universal rule of a normalised table: No. This is not an universal rule, and the SET column type _should_ be used when it is the best choice. IMO. I suppose the rule you are referring to is the first normal form (1NF). > You cannot, no matter what, and should not, store more than one Either you can but shouldn't, or you can't... ;) > value in a single cell of a database table. And how would you store a string? A single char per row? ;) This would depend on how you define "one value". It is usefull to define a string as "one value", and not as "a list of characters", because the latter would be in conflict with 1NF. Another example is names: you would often store "Baklund, Roger" in a single column, even if it is (at least) two values: family name and given name. Similarly, a product number "X-23/b4" may contain "multiple values", but it is still usefull to store it in a single column, and define it as the single value "product#". Maybe the "X-" prefix means this product is in some special category, you would still keep a 'prod_cat' column, thus "breaking the rules" of redundancy. This is normal, most databases of some size have such redundancies, it won't prevent you from taking advantage of normalization, and you would still call the database "normalized". Normalization is not an "exact science" in the real world, you have to use what works best. If the five checkboxes are static, they will never change, then I see no reason not to use a SET column, unless, of course, he needs to index on this. And I don't think he needs to, because he said what he needed was statistics for the whole table, thus he needs to read the entire table anyways. To stay within 1NF you can simply define a new term "checkboxvalue", which is an integer between 0 and 31, representing all possible combinations of the five checkboxes. This integer can be stored in a SET column, which also gives you a nice "string interface" to the bit manipulation arithmetic you otherwise would need to perform. And you get both: you can still use bit arithmetic if you like. You _can_ use an index on a SET column, but only for exact matches and ranges, not when searching for "anyone who have checked 'car'". An exception to this is the last item in the set, because that is assigned the highest value. In the example in this thread, 'beetle' is the last value, representing 16, so an indexed range check could be used to find anyone who have or have not checked 'beetle': "WHERE choiceflag >= 16" or "WHERE choiceflag < 16". > Even if you did, it is going to be really hard later on to change the > names of the SETS (this would contradict with the user's choice), and is > going to be still harder to add another choice to your list. Well, ALTER TABLE is not very hard... but not needing to is even easier, of course. :) He said the list would not change. > The best I would recommend, though it takes up more space in the table, > is to have a separate column in another table with a one-to-one join, > and each column as each choice, and each column with the ENUM type of > true/false. one-to-one? why not five columns in the original table? And why not go all the way with two new tables: choices (choiceid tinyint,choice varchar(255)) user_choices (userid int,choiceid tinyint) > Think before you choose, for you don't want to be sorry later. That is allways a good advice. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on best table structure
Roger Baklund unknowingly asked us: What would be a good way to deal with the following... I have a form that has 5 checkboxes on it, lets say the checkboxes are for categories, and more than one can be selected. For example: please tell is what brochure you want [] car [] boat [] truck [] SUV [] beetle I have been asked to provide statistics on how many total form submissions there are per month. This part seems pretty simple, I just add a new record every time the form is sent. They also want to know stats on which categories were picked as well. I don't know if I should create one table to store just the fact that a form has been posted, and then another to store the 5 above values. I don't see the 5 categories being changed, so I could create 5 fields in one table, and tally them that way, or I could create one field and put the actual value in that field and tally them that way. Any suggestions are appreciated. Consider the SET column type: Yes, SET seems a good way, as you're going to allow multiple choices in the checkboxes. But for a couple of reasons, SET is not recommend. 1. It introduces fragmentation, and indexes are pretty hard. 2. It defies the universal rule of a normalised table: You cannot, no matter what, and should not, store more than one value in a single cell of a database table. Even if you did, it is going to be really hard later on to change the names of the SETS (this would contradict with the user's choice), and is going to be still harder to add another choice to your list. The best I would recommend, though it takes up more space in the table, is to have a separate column in another table with a one-to-one join, and each column as each choice, and each column with the ENUM type of true/false. Think before you choose, for you don't want to be sorry later. -- [ Rajesh Kumar ] __ Meet the guy at http://www.meetRajesh.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on best table structure
* Scott Haneda > What would be a good way to deal with the following... > > I have a form that has 5 checkboxes on it, lets say the > checkboxes are for categories, and more than one can be > selected. > > For example: > please tell is what brochure you want > [] car > [] boat > [] truck > [] SUV > [] beetle > > I have been asked to provide statistics on how many total form > submissions there are per month. This part seems pretty simple, > I just add a new record every time the form is sent. > > They also want to know stats on which categories were picked as > well. I don't know if I should create one table to store just > the fact that a form has been posted, and then another to store > the 5 above values. > > I don't see the 5 categories being changed, so I could create 5 > fields in one table, and tally them that way, or I could create > one field and put the actual value in that field and tally them > that way. > > Any suggestions are appreciated. Consider the SET column type: http://www.mysql.com/doc/en/SET.html > There are at two nice things SET provides: 1) the compact storing, you would pack all five checkboxes in a single byte, and 2) you can use binary operations. The downside is you can't index on a SET column efficiently, or more accurately: mysql can't use the index when you use binary operators or the LIKE operator with "%" as the first character. But that's probably not relevant in your case. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on best table structure
At 03:30 PM 8/20/2003 -0700, Scott Haneda wrote: What would be a good way to deal with the following... I have a form that has 5 checkboxes on it, lets say the checkboxes are for categories, and more than one can be selected. For example: please tell is what brochure you want [] car [] boat [] truck [] SUV [] beetle I don't see the 5 categories being changed, so I could create 5 fields in one table, and tally them that way, or I could create one field and put the actual value in that field and tally them that way. Any suggestions are appreciated. I'm gonna go out on a limb here. My intuition tells me to look for the opportunity to add some data to this process... For example, what if a record were created for each brochure that needed to be processed, and suppose that in some back-end application the person fulfilling these requests completed the data in the record... Then you might justify making a separate table for each brochure (or one with a two column key)... Then you would not waste space, you could gather statistics easily with joins, and you'd satisfy both processes at once... then again, if none of that back-end process exists, then it's probably simplest to just make an integer column and bit-map the check-boxes into it (if you want to save space)... or if you don't care about space just create a true/false column for each check box. The short answer is - more info is needed to pick the best approach. HTH, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seeking advice on best table structure
What would be a good way to deal with the following... I have a form that has 5 checkboxes on it, lets say the checkboxes are for categories, and more than one can be selected. For example: please tell is what brochure you want [] car [] boat [] truck [] SUV [] beetle I have been asked to provide statistics on how many total form submissions there are per month. This part seems pretty simple, I just add a new record every time the form is sent. They also want to know stats on which categories were picked as well. I don't know if I should create one table to store just the fact that a form has been posted, and then another to store the 5 above values. I don't see the 5 categories being changed, so I could create 5 fields in one table, and tally them that way, or I could create one field and put the actual value in that field and tally them that way. Any suggestions are appreciated. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
