SEO

I have an email client application where a person can have many
mailboxes. Thus, I have "person" and "mailbox" tables:

create table person (
id SERIAL PRIMARY KEY,
name text
);

create table mailbox (
id SERIAL PRIMARY KEY,
name text,
owner integer NOT NULL REFERENCES person ON DELETE CASCADE,
UNIQUE ( owner, id ) -- see below
);

Now, a person might like to have a default mailbox that opens up when
they start the application. So I add a column to the person table:

ALTER TABLE person ADD column default_mailbox integer
REFERENCES mailbox ON DELETE SET NULL;

Of course, I want to make sure that the person actually owns that
mailbox, so add a constraint (which is why the UNIQUE is required
above).

ALTER TABLE person ADD CONSTRAINT default_mailbox_owner
FOREIGN KEY (id, default_mailbox) REFERENCES mailbox(owner, id);

Is this a sane way to set up a "default mailbox"?

The other option is to have a column on the mailbox table to flag that
it is a default_mailbox -- but then I'd have to ensure there's only
one column for each "person" flagged that way.



Two more related questions:

First, if I delete a default mailbox the default_mailbox will be set set
NULL. If instead I never delete a mailbox but rather add a boolean
column "deleted". ON DELETE is no longer any help. Is my
only option to use a trigger set NULL any default_mailbox column(s)
that reference the mailbox when it is set "deleted"?



Second question. So, after a while the obvious problem happens and
users have too many mailboxes and they want a way to group them into
"mailbox_groups" that are containers for mailboxes. So, we create a
new table and alter the mailbox table. Each user has their own set of
mailbox groups so I include an "owner" column:

create table mailbox_group (
id SERIAL PRIMARY KEY,
name text,
owner integer NOT NULL REFERENCES person ON DELETE CASCADE,
);

ALTER TABLE mailbox ADD COLUMN mailbox_group int
NOT NULL REFERENCES mailbox_group(id);

Now, I'm wondering about the sanity of the design since this results
in "owner" columns on both the mailbox and mailbox_group tables. Do I
add a constraint to make sure that mailbox.mailbox_group references a
group that has a matching owner?

Or do I remove the "owner" column from mailbox table and alter all my
access to mailbox to now do a join with the mailbox_group table (to
find the owner)?


(Or do I wonder why I didn't expose the database only through views in
the first place?)

Thanks,



--
Bill Moseley
moseley@hank.org


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> The other option is to have a column on the mailbox table to flag that
> it is a default_mailbox -- but then I'd have to ensure there's only
> one column for each "person" flagged that way.

- is_default BOOL column in mailbox table
- conditional index :

UNIQUE INDEX ON mailboxes( owner ) WHERE is_default = 't'

> Second question. So, after a while the obvious problem happens and
> users have too many mailboxes and they want a way to group them into

Looks like a tree. Why not use a LTREE ?

---------------------------(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