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
[EMAIL PROTECTED]
---------------------------(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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
