Schema sanity check
От | Bill Moseley |
---|---|
Тема | Schema sanity check |
Дата | |
Msg-id | 20070521214214.GA10801@hank.org обсуждение исходный текст |
Ответы |
Re: Schema sanity check
(PFC <lists@peufeu.com>)
|
Список | 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@hank.org
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Perl + Determine active connections on Pg 8.0.x and Pg 8.2.x