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

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