From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Another constant in foreign key problem.
Date: Wed, 22 Feb 2012 11:18:23 -0000
Message-ID: <F5CCFEA33B294832A9CEB79E65F556CA@marktestcr.marktest.pt> (raw)
References: <201202221035.11963.gary.stainburn@ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, gary.stainburn@ringways.co.uk
Subject: Re: Another constant in foreign key problem.
In-Reply-To: <F5CCFEA33B294832A9CEB79E65F556CA@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox