Re: Table design - unknown number of column

On Thursday 9. November 2006 09:34, Richard Ollier 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.
>
>What would be the cleanest and most recommended solution ?

I'd go for alternative b. If the only flag values are 0 and 1, you can
skip the value column entirely and just enter the records where the
value is 1.

Rather than having the flag names in this table, I'd break the names out
in another table flag_types with the columns flag_id and flag_name.

Then, your flag table becomes a standard many-to-many crosstable:

create table flags (
product_fk integer references products (product_id),
flag_fk integer references flag_types (flag_id)
);

select product_fk, flag_name from flags, flag_types
where flags.flag_fk = flag_types.flag_id
and product_fk = 42

will give you every flag that is set for this product. If you need to
set a flag for any product to 0, just delete the row:

delete from flags where flag_fk = 120 and product_fk = 42;
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE