DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=EadA80QpvmPp0ePpbS6IQK0LrlsVI+TS8fdwizZOcWvThuQPIVSQ53wtvET4cxkG5UFsCoeiJ+bEsakbSCSiP3CPc7exlwgTKyAag59e9mNpAJtzR1jiHbKeN+g3K09Ww5u9VrREcZFLSkKxgy/zpew0eG3dP3kvtvqcltgV4wM=
Message-ID: <b0a4f3350803070733t17afacccped2996b61d264704@mail.gmail.com>
Date: Fri, 7 Mar 2008 15:33:28 +0000
From: "Jamie Tufnell" <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Composite UNIQUE across two tables?
In-Reply-To: <CMEFJOLLJJDJKKLGEJJMKEJHCAAA.ray@madigans.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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.