From: | "Jamie Tufnell" <diesql(at)googlemail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Composite UNIQUE across two tables? |
Date: | 2008-03-07 15:33:28 |
Message-ID: | b0a4f3350803070733t17afacccped2996b61d264704@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jamie Tufnell | 2008-03-07 15:46:53 | Re: Composite UNIQUE across two tables? |
Previous Message | Tom Lane | 2008-03-07 13:47:41 | Re: UPDATE .. FROM |