Re: Table design - unknown number of column

On 11/9/06, Richard Ollier <r.ollier@tequila.co.jp> wrote:
> Hello,
>
> For a project I have a table containing products and flags.
> The columns of this table are of 2 kinds :
> - Not null data (id, column1, column2)
> - Flags (100 different flags set to 1 or 0)
>
> Over the time the number of flag will increase from 100 to 200 or more.
>
> So I have 2 choices :
> - Use a unique table and redesign this table and my application each
> time I need to add a flag
>
> - Split this table in 2 tables : a table containing the Not null data
> and a table containing 3 columns (id, flag_name, flag_value). But in
> this case how can I do a select where (flag_a=1 and flag_c=0 and
> flag_k=1...) ? I would like to have 200 or more joins on the main table.

this is much cleaner.  you have a table of products, a table of flags,
and a crossref table, lets call it product_flags.  if you want to
query procucts with a very specific set of flags, you can write a
query with a join to get it out or use arrays.

maybe if you query out products with realated flags alot you can make
another table, product_flag_group which you can use to simplify things
a bit.

merlin