List:General Discussion« Previous MessageNext Message »
From:Rajesh Kumar Date:August 21 2003 3:47pm
Subject:Re: Seeking advice on best table structure
View as plain text  
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/

Thread
Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)19 Aug
  • RE: Need help optimizing query, awfully slow on only 20000 recordsJim Smith20 Aug
RE: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)20 Aug
  • RE: Need help optimizing query, awfully slow on only 20000 recordsMichael S. Fischer21 Aug
RE: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)21 Aug
  • RE: Need help optimizing query, awfully slow on only 20000 recordsMichael S. Fischer21 Aug
  • Seeking advice on best table structureScott Haneda21 Aug
    • Re: Seeking advice on best table structurePete McNeil21 Aug
    • Re: Seeking advice on best table structureRoger Baklund21 Aug
      • Re: Seeking advice on best table structureRajesh Kumar21 Aug
        • Re: Seeking advice on best table structureRoger Baklund21 Aug
Re: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)21 Aug
RE: Need help optimizing query, awfully slow on only 20000 recordsCarmel Entertainment)21 Aug
  • MySQL 4.0.13 Memory problem under heavy loadKayra Otaner21 Aug
    • RE: MySQL 4.0.13 Memory problem under heavy loadMichael S. Fischer21 Aug
    • Re: MySQL 4.0.13 Memory problem under heavy loadDan Nelson21 Aug
  • Re: MySQL 4.0.13 Memory problem under heavy loadMartin Gainty21 Aug