Thread: Schema sanity check

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

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