Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 11B4D2E038D for ; Fri, 7 Mar 2008 04:16:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 87506-02 for ; Fri, 7 Mar 2008 04:16:36 -0400 (AST) X-Greylist: delayed 00:30:01.454415 by SQLgrey-1.7.5 Received: from interscan.indicator.com (unknown [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 1221B2E01FB for ; Fri, 7 Mar 2008 04:16:38 -0400 (AST) Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise; Fri, 07 Mar 2008 08:46:36 +0100 Message-Id: <47D100E0.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Fri, 07 Mar 2008 08:46:24 +0100 From: "Bart Degryse" To: "Jamie Tufnell" , Subject: Re: Composite UNIQUE across two tables? References: In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartCDEB57C0.1__=" X-imss-version: 2.050 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/37 X-Sequence-Number: 30365 --=__PartCDEB57C0.1__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable create a function that, given the siteid returns the sitegroupid create a unique index on username and that function >>> "Jamie Tufnell" 2008-03-06 20:04 >>> 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=3Dpostgresql.org&extra=3Dpgs= ql-sql --=__PartCDEB57C0.1__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable

create a function that, given the siteid returns the sitegroupid

=

create a unique index on username and that function

>>>= ; "Jamie Tufnell" <diesql@googlemail.com> 2008-03-06 20:04 >>&g= t;
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.
Ea= ch 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,
&nbs= p;   PRIMARY KEY (id)
);

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

CREATE TABLE users (
&nbs= p; id serial,
    site_id integer not null,
&nbs= p;   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.

--
Sent = via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes = to your subscription:
http://mail.postgresql.org/mj/m= j_wwwusr?domain=3Dpostgresql.org&extra=3Dpgsql-sql

= --=__PartCDEB57C0.1__=--