From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: Another constant in foreign key problem.
Date: Wed, 22 Feb 2012 11:25:15 +0000
User-Agent: KMail/1.9.10
References: <201202221035.11963.gary.stainburn@ringways.co.uk>
	<F5CCFEA33B294832A9CEB79E65F556CA@marktestcr.marktest.pt>
In-Reply-To: <F5CCFEA33B294832A9CEB79E65F556CA@marktestcr.marktest.pt>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201202221125.15486.gary.stainburn@ringways.co.uk>

Sorry,

I missed one table.  The users_facility_levels table is used to link the three 
tables listed below to give the lever per user/facility.


On Wednesday 22 February 2012 11:18:23 Oliveiros d'Azevedo Cristina wrote:
> Hi, Gary,
>
> I'm answering by editing your e-mail
> ______
>
> >I have three tables,
> >
> > users - all users of my web site
> > facilities - facilities available on my web site
> > facility_levels - access levels per user/facility.
> >
> > One of my facilities is a document library (f_id = 22)
> > For this facility I have the levels
> >
> > select * from facility_levels where fl_f_id=22 order by fl_level;
> > fl_f_id | fl_level |   fl_desc
> > ---------+----------+--------------
> >      22 |        1 | Read Only
> >      22 |        2 | Add Versions
> >      22 |        3 | Amend
> > (3 rows)
> >
> >
> > This sets the global access level for the Document Library per user.
>
> * It is not clear for me how this sets the global access level per user.
> Shouldnt the facility_levels table have a u_id field, foreign key from
> users table? And thus becoming an associative table between users and
> facilities?
>
> > I now want to add authentication control on a document or folder level.
> > For
> > this I need to create a table library_document_user_level
> >
> > u_id - user id
> > ld_id - library document id
> > fl_level - level
> >
> > The foreign key constraint on fl_level needs to check facility_levels for
> > fl_f_id = 22 as well as fl_level existing.
>
> * I may not be fully understanding your problem, but
> library_document_user_level shouldn't have a field named  fl_f_id, to
> identify which facility the document/folder belongs to?
> Had it such a field, you could do something like
> FOREIGN_KEY (fl_f_id,fl_level)  REFERENCES facility_levels (fl_f_id,
> fl_level)
>
>
> Just my two cents
>
> Best,
> Oliveiros
>
> > I've googled this but can't find a suitable solution. Can anyone help
> > please.
> > --
> > Gary Stainburn
> > Group I.T. Manager
> > Ringways Garages
> > http://www.ringways.co.uk
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql


-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk