Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0C51B2E2F31 for ; Thu, 6 Mar 2008 21:10:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30983-01 for ; Thu, 6 Mar 2008 21:10:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.156]) by postgresql.org (Postfix) with ESMTP id 5A4E82E2F01 for ; Thu, 6 Mar 2008 21:10:18 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 22so160452fge.23 for ; Thu, 06 Mar 2008 17:10:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=pohAJUWyFByMz48Ul1cKzFlqm5ylqk5e8ptqcCATl5o=; b=YovUN5tjAcJeBFWf2ODbsAFpK384jXjmZPVZgPYs05Gx6mRU3/GGrQFTh4/vcPSoUh8i2718PfJakfvCVQeFMM1qQoyeNI4ACCHCnJgFzonGIL4rF89TuvR+0DJ1RnrDBdSZ1A5KJvkqGil0YmvXZpJAz1fC0iGwD00nXnck7o0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nuQwC9hPry2k/swPqIUPHV8v/Jx6WAQWGvr9Hk3CEZbVZy6TgSiQTTgK/UdTwrp/J4xA0VTWxcfCJzGQIocPGDfiBYh5snlvr6YO2gSwqGKbJu6ixSb/GElBYXv/aFikL/2OGSuXZw4qKQjq7g1PIQLpT/1wbdPbLedf3ywAL0g= Received: by 10.86.86.12 with SMTP id j12mr605649fgb.33.1204852216861; Thu, 06 Mar 2008 17:10:16 -0800 (PST) Received: by 10.86.86.18 with HTTP; Thu, 6 Mar 2008 17:10:14 -0800 (PST) Message-ID: Date: Fri, 7 Mar 2008 01:10:14 +0000 From: "Jamie Tufnell" To: "Ray Madigan" Subject: Re: Composite UNIQUE across two tables? Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200803/35 X-Sequence-Number: 30363 Hi Ray, Thanks for your reply! On 3/6/08, Ray Madigan wrote: > I don't think I understand. You have a constraint that a user has implied > access to any site in the group, explain why you think it would be wrong to > have the group_id as an instance variable fro the user. Otherwise whenever > the user is in a site in the site group other then the specific site > represented by the user.site_id the query has to go join with the site to > find the site group. That's true. I'll try to explain why it feels wrong... I already have site_id in the users table and I can determine the site_group_id from that. So it seems redundant to me, to store site_group_id for each user as well. Also, I'm not sure how I would enforce that the site_group_id added to the users table would correspond correctly with the site_id (as per the sites table). Perhaps I would make a composite foreign key? I've never come up against this situation before, and because of the reasons above, I'm getting the feeling there might be a better way to design this. Cheers, J.