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(at)hank(dot)org