I'm sure there's a point where you'd be saving a "substantial" amount
of disk space using a non-normalized scheme, but, like you say, you'd
be missing out on other things. In general, disks are cheap while the
man hours used to try to fix data corruption is not.
On May 15, 2007, at 7:35 AM, Bill Moseley wrote:
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match