List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:August 21 2003 2:42am
Subject:Re: Seeking advice on best table structure
View as plain text  
* 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:

<URL: 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

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