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