Hi Ray, On 3/7/08, Ray Madigan <[EMAIL PROTECTED]> 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
