Re: Performance issues of one vs. two split tables.

Can anyone provide input on this question?  I'm curious how to look at
this from a disk and memory usage perspective.  Would using a bit
column type help much?

I'm not thrilled by the loss of referential integrity.

On Mon, May 14, 2007 at 01:37:18PM -0700, Bill Moseley wrote:
>
> Say I have a table "color" that has about 20 different rows ("red",
> "blue", "green", etc.).  I want the user to be able to select zero or
> more favorite colors.  I would typically use a link table:
>
>     create table favorite_colors (
>         color       int references color(id),
>         user        int references user(id)
>     );
>
> Now, that table can have a large number of rows if I have a large
> number of users and if everyone likes all the colors.
>
> For some value of "large", is there a time when one might consider
> using a single column in the user or user_prefs table to represent
> their color choices instead of a link table?
>
>     table user_prefs (
>         ...
>         favorite_colors     bit varying,
>         ...
>     );
>
> Where each bit represents the primary key of the colors table.
>
> Seems like poor design, but I'm wondering if there might be overriding
> concerns at times.
>
> For example, if I have 1 million users and they each like all colors
> and thus have a 20 million row link table how much space would be
> saved by using a bit column as above?

--
Bill Moseley
moseley@hank.org