Skip site navigation (1) Skip section navigation (2)

Re: Another constant in foreign key problem.

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Gary Stainburn" <gary(dot)stainburn(at)ringways(dot)co(dot)uk>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: Another constant in foreign key problem.
Date: 2012-02-22 11:18:23
Message-ID: F5CCFEA33B294832A9CEB79E65F556CA@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 


In response to

Responses

pgsql-sql by date

Next:From: Gary StainburnDate: 2012-02-22 11:25:15
Subject: Re: Another constant in foreign key problem.
Previous:From: Gary StainburnDate: 2012-02-22 10:35:11
Subject: Another constant in foreign key problem.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group