DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=oRx7C3mryJ0dWYS3e+vfvz6rRO4X33rjtie71WvBFAyvkGozEUwENnI+FLCUfYwwR+1AhAEYdGyjBblqruV+137d8y4z9mGj0hFPxP+B26cpjjOeyeNCHVOA0e2KVwWMAVfM+pw8ZHckIUrpPt+Nto7XGb5699WOHcBDpLULCmo=
Message-ID: <b0a4f3350803061104s89656bcl739e1aa1cdcb6e3a@mail.gmail.com>
Date: Thu, 6 Mar 2008 19:04:40 +0000
From: "Jamie Tufnell" <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Composite UNIQUE across two tables?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I'm remodeling our authentication tables at the moment to accomodate
future changes.  I've hit a wall and thought I'd ask here for some
help.  Hopefully I'm just missing something simple.

I'll do my best to explain the domain..

We have users, sites, and groups of sites.

Usernames should be unique within a site group.
Each user is tied to one, and only one, site.
Each site belongs to exactly one group, and one group can contain
many sites. (some sites are actually groupless in reality, but at
one point in my design I figured it might make it easier to force
them to be in a group of 1 -- perhaps that's part of my problem?).

A user has implied permission to access any site in their group of
sites.

So far this is what I have:

CREATE TABLE site_groups (
    id serial,
    name varchar(120) not null,
    PRIMARY KEY (id)
);

CREATE TABLE sites (
    id serial,
    site_group_id integer not null,
    name varchar(120) not null,
    PRIMARY KEY (id),
    FOREIGN KEY (site_group_id) REFERENCES site_groups (id)
);

CREATE TABLE users (
    id serial,
    site_id integer not null,
    username varchar(120) not null,
    PRIMARY KEY (id),
    FOREIGN KEY (site_id) REFERENCES sites (id)
);

The problem is the "Usernames should be unique within a site group."

I could include a site_group_id FK in the users table, and put a
UNIQUE(username, site_group_id), but it feels like I'm doing
something wrong here since the user's site_group_id is already implied
by the user's site_id.

When users login I get their username, password and site name.
Without a UNIQUE constraint on username+site_group_id, it's possible
I'll get more than one user id matching the same login information
which shouldn't happen.

I suppose I could write a trigger to enforce this uniqueness, but it
just feels like I'm probably doing something wrong elsewhere.

Any help/abuse greatly appreciated :-)

Cheers,
J.