Thread: Composite UNIQUE across two tables?

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),
FOREIGNKEY (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),
FOREIGNKEY (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.

Hi Ray,

Thanks for your reply!

On 3/6/08, Ray Madigan <ray@madigans.org> 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.

Em Thursday 06 March 2008 22:10:14 Jamie Tufnell escreveu:
>
> 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?

Or you manage site_group_id from a trigger.  When the user creates / chooses a 
site_id and updates/inserts this information then you fill site_group_id with 
a trigger.  This way you guarantee the correctness of this parameter.

> 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.

Again, a trigger can do that.  Slower than denormalizing the database, but it 
can be done.


-- 
Jorge Godoy      <jgodoy@gmail.com>

Hi Bart,
I'm following this topic with interest.
Could you describe me more how you design an unique index with both a column
name and a function name by an example
Thx.
Sebastien

________________________________________
De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
De la part de Bart Degryse
Envoyé : vendredi 7 mars 2008 08:46
À : Jamie Tufnell; pgsql-sql@postgresql.org
Objet : Re: [SQL] Composite UNIQUE across two tables?

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 >>>
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=postgresql.org&extra=pgsql-sq
l

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.

Hi Bart,

On 3/7/08, Bart Degryse <Bart.Degryse@indicator.be> wrote:
> I haven't tested these two statements, but I'm using exactly this
> concept on some tables myself.
> My equivalent of your users table contains some 3,000,000 records.
> My equivalent of your sites table contains some 150,000 records.
> And it works fine...
>
> CREATE OR REPLACE FUNCTION "fnc_idx_sitegroupid" (p_siteid
> sites.id%TYPE) RETURNS site_groups.id%TYPE AS
> $body$
> DECLARE
> v_sitegroupid site_groups.id%TYPE ;
> BEGIN
> SELECT site_group_id INTO v_sitegroupid FROM sites WHERE id =
> p_siteid;
> RETURN v_sitegroupid;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
> INVOKER;
>
> CREATE UNIQUE INDEX "users_unq" ON "users"
> USING btree ("username", (fnc_idx_sitegroupid(site_id)));

Thank you for your suggestion and example!  I really like this idea
but I haven't been able to get it to work.

When I try to create the index I get the following error:

ERROR:  functions in index expression must be marked IMMUTABLE

After consulting the docs
(http://www.postgresql.org/docs/8.2/static/sql-createfunction.html)
I get the impression I shouldn't declare this function IMMUTABLE since
it queries the database?  It seems to me it should be STABLE.

Out of curiosity, I declared it IMMUTABLE and it worked for the
purposes of my small, isolated test,.

Am I opening myself up to problems by doing this?

Cheers,
J.

Hi Jorge,

On 3/7/08, Jorge Godoy <jgodoy@gmail.com> wrote:
> Em Thursday 06 March 2008 22:10:14 Jamie Tufnell escreveu:
> > 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?
>
> Or you manage site_group_id from a trigger. When the user creates / chooses
> a site_id and updates/inserts this information then you fill site_group_id
> with a trigger. This way you guarantee the correctness of this parameter.

Hmm.  So your advice would be to duplicate the
site_group_id field into the users table as well?

That looks to be the consensus.. so I'm not sure why it feels
like I'm doing something wrong :-)

Thanks for your input!

J.

On 3/7/08, Jamie Tufnell <diesql@googlemail.com> wrote:
> On 3/7/08, Bart Degryse <Bart.Degryse@indicator.be> wrote:
> > I haven't tested these two statements, but I'm using exactly this
> > concept on some tables myself.
> > My equivalent of your users table contains some 3,000,000 records.
> > My equivalent of your sites table contains some 150,000 records.
> > And it works fine...
> When I try to create the index I get the following error:
>
> ERROR: functions in index expression must be marked IMMUTABLE
>
> Out of curiosity, I declared it IMMUTABLE and it worked for the
> purposes of my small, isolated test,.
>
> Am I opening myself up to problems by doing this?

Does anyone have any comment on this?  I've done some more testing and
have yet to run into a problem.

I'd be really interested to hear some opinions on this.

Cheers,
J.

Hello Jamie,

I vote against duplicating site_group_id in the users table and the
proposed unique constraint with a function. Because all those might
fail, if you ever want to change the relationship between a site and a
site group.

My advise would be to have two triggers for insert/update on the site
and users table that check the uniqueness of the username with the
site_group. A have made some tests with inserts and updates on the
existing users and sites and these two functions seem to work.

One remark about your schema: If you use PostgreSQL, use the "text"
datatype for strings, since you don't limit yourself with the string
length. For my tests, I have modified your posted schema a bit, to
unify all column names to "name". You should set up an extensive
testcase if you haven't done already which should check every
combination of insert, update and delete on the three tables and see
if those are supposed to work, or should be restricted because of your
uniqueness constraints.

CREATE TABLE site_groups (  id serial primary key,  name text not null
);

CREATE TABLE sites (  id serial primary key,  site_group_id integer not null references site_groups,  name text not
null
);

CREATE TABLE users (  id serial,  site_id integer not null references sites,  name text not null
);

create or replace function user_check_unique_site_group()
returns trigger
as $$
declare c int; sg int;
begin -- get site_group id from site select into sg site_group_id from sites where id = NEW.site_id;
 -- check if we find any equal user names in the site group select into c count(*) from users, sites where
users.site_id= sites.id   and sites.site_group_id = sg   and users.name = NEW.name;
 
 -- nothing found, this user name is ok if c = 0 then   return NEW; end if;
 raise exception 'username is not unique with site group'; return NULL;
end;
$$ language plpgsql;

create trigger user_check_unique_site_group
before update or insert
on users
for each row
execute procedure user_check_unique_site_group();

create or replace function sites_check_unique_username()
returns trigger
as $$
declare c int;
begin -- if the site group is unmodified we're safe if NEW.site_group_id = OLD.site_group_id then   return NEW; end
if;
 -- check if the same username is in the old and new site group select into c count(*) from users, sites where
users.site_id= sites.id   and sites.site_group_id = NEW.site_group_id   and users.name in (                      select
users.name                     from users, sites                      where users.site_id = sites.id
   and sites.site_group_id = OLD.site_group_id                     );
 
 -- nothing found, we're safe if c = 0 then   return NEW; end if;
 raise exception 'username is not unique with site group'; return NULL;
end;
$$ language plpgsql;

create trigger sites_check_unique_username
before update
on sites
for each row
execute procedure sites_check_unique_username();

-- 
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org

On Tue, Mar 11, 2008 at 2:03 PM, Dirk Jagdmann <jagdmann@gmail.com> wrote:
>  For my tests, I have modified your posted schema a bit, to
>  unify all column names to "name".

OT and IMHO, this is not good practice. Column names should be unique
and descriptive throughout the entire schema.

Hi Dirk,

On 3/11/08, Dirk Jagdmann <jagdmann@gmail.com> wrote:
> I vote against duplicating site_group_id in the users table and the
> proposed unique constraint with a function. Because all those might
> fail, if you ever want to change the relationship between a site and a
> site group.

Good point!

> My advise would be to have two triggers for insert/update on the site
> and users table that check the uniqueness of the username with the
> site_group. A have made some tests with inserts and updates on the
> existing users and sites and these two functions seem to work.

I think this is the way that I'll go.  I'd hoped to somehow express this
solely in the design, if you know what i mean (e.g. without writing
SPs), but it looks like this is the best way to do it.

> One remark about your schema: If you use PostgreSQL, use the "text"
> datatype for strings, since you don't limit yourself with the string
> length.

[snip]

For some reason I assumed varchar had an advantage over text,
but a quick check of the docs suggests that's not the case. Thanks
for this tip! :-)

Thank you for your taking the time to write this up, it's very much
appreciated.

Cheers,
J.

>  > My advise would be to have two triggers for insert/update on the site
>  > and users table that check the uniqueness of the username with the
>  > site_group. A have made some tests with inserts and updates on the
>  > existing users and sites and these two functions seem to work.
>
>  I think this is the way that I'll go.  I'd hoped to somehow express this
>  solely in the design, if you know what i mean (e.g. without writing
>  SPs), but it looks like this is the best way to do it.

Well I thought about that, but finally came to the conclusion, that
standard SQL constraints can not express this inter-table
relationships. As I'm not a fan of (artificially) breaking up tables I
just wrote those two pl/pgsql functions, because I'd rather have a
simple table design and some complicated constraint checking functions
than the other way.

>  Thank you for your taking the time to write this up, it's very much
>  appreciated.

Most people reading this list like to think about/learn from other
people's problems.

-- 
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org