Re: Composite UNIQUE across two tables?

Hi Ray,

On 3/7/08, Ray Madigan <ray(at)madigans(dot)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.