Is there any benefit of splitting up a table into two tables that will
always have a one-to-one relationship?
Say I have a "user" table that has first, last, email, password, and
last_accessed columns. This user table will be accessed often. (It's
not really "user", but that's not important in this discussion)
Say that there's also about 10 columns of settings or preferences for
each user. Are there any cases or reasons to have a separate
"user_preferences" table vs. just placing all the columns together in
one table?
Another related question:
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
