Schema sanity check

From: Bill Moseley <moseley(at)hank(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Schema sanity check
Date: 2007-05-21 21:42:15
Message-ID: 20070521214214.GA10801@hank.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)hank(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message PFC 2007-05-21 21:53:54 Re: Schema sanity check
Previous Message Tom Lane 2007-05-21 21:14:47 Re: Perl + Determine active connections on Pg 8.0.x and Pg 8.2.x