Thread: Table design - unknown number of column
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 ? Thanks a lot for your help Richard
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.
Having 200 flags as 200 fields... Writing queries on that is going to be
painful.
I would probably look at bitwise operations, although 200 bits is quite
a bit larger than a bigint. Maybe an array of ints would work...
I don't suppose you expect 2^200 different combinations, maybe some of
the flags can be grouped together?
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
On fim, 2006-11-09 at 10:56 +0100, Alban Hertroys wrote: > Richard Ollier wrote: > > 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. > > Having 200 flags as 200 fields... Writing queries on that is going to be > painful. > > I would probably look at bitwise operations, although 200 bits is quite > a bit larger than a bigint. Maybe an array of ints would work... maybe the BIT VARYING datatype could be useful. http://www.postgresql.org/docs/8.1/interactive/datatype-bit.html gnari
Alban Hertroys wrote: > 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. > > Having 200 flags as 200 fields... Writing queries on that is going to > be painful. > > I would probably look at bitwise operations, although 200 bits is > quite a bit larger than a bigint. Maybe an array of ints would work... > > I don't suppose you expect 2^200 different combinations, maybe some of > the flags can be grouped together? Thanks for your time ! Even if I know it won't happen, there is 2^200 different combinations possible as products are very different one from each others... In the case I use an array of int, is the search fast and efficient ? I actually never had to use an array in postgres till now..
I think the typical way of attacking a problem would be a second and third table. The second table would look like: flat_type table flag_type_id flag_type (like the column name in your original table) flag_type_description (BONUS: you can describe each flag) product_flag table product_id (fk to your original table) flag_type_id (fk to the flag_type table) flag_value (0, 1, or whatever you want to store here) The advantages with this method are several: 1) No wasted storage for all those NULLs where a flag is not needed 2) Should be very fast to lookup by product_id to get all flags 3) You can expand to an arbitrary number of flags 4) Your schema remains normalized Sean
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
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