From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Composite UNIQUE across two tables?
Date: Fri, 7 Mar 2008 15:33:28 +0000
Message-ID: <b0a4f3350803070733t17afacccped2996b61d264704@mail.gmail.com> (raw)
In-Reply-To: <CMEFJOLLJJDJKKLGEJJMKEJHCAAA.ray@madigans.org>
References: <b0a4f3350803061710y2d3391cdwa54d7dcf4af5692f@mail.gmail.com>
<CMEFJOLLJJDJKKLGEJJMKEJHCAAA.ray@madigans.org>
Hi Ray,
On 3/7/08, Ray Madigan <ray@madigans.org> wrote:
> How I think about it.
>
> A user has access to a site and all of the sites within the site group that
> the site is in.
>
> if you reword your condition
>
> A user has access to all of the sites in a site group with a default defined
> by site_id. Then there is no problem having both variables in the table.
One field in the users table (site_id) implicitly ties the user to a
site_group_id.
One field in the users table (site_group_id) explicitly ties the user
to a site_group_id.
The problem I have (or had.. read below) was enforcing that those
site_group_id's are equal.
> Also, you have to trade off the cost of the table join to get the group_id
> in all of the queries as opposed to the extra integer required. My thought
> is that the extra Integer is small compared to the number of wueries that
> have to run and would then adopt the second wording of the constraint.
>
> Even if you come up with an alternative, composite key you will still have
> to deal with all of the table joins. The table joins isn't a big deal, but
> it is unnecessary.
When I asked about a composite FK in my previous message, I'd planned
to use it in addition to your solution (not in place of)... the idea
being to solve the problem mentioned above.
What I was thinking is something like this .. I'm interested to hear
your thoughts on this:
users table:
FOREIGN KEY (site_id, site_group_id) REFERENCES sites (id, site_group_id)
sites table:
UNIQUE (id, site_group_id)
Assuming that's going to work... then I could use your suggestion of
adding site_group_id to the users table. As you said, that will make
writing SELECTs a lot simpler.
How would I handle INSERTs / UPDATEs though without having to always
specify both fields? I have a feeling it will require some functions
being called by triggers / default values. That's all doable, but I
wonder if there's a way of expressing these relationships without
having to duplicate site_group_id.
Cheers,
J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: Composite UNIQUE across two tables?
In-Reply-To: <b0a4f3350803070733t17afacccped2996b61d264704@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox