pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother constant in foreign key problem.
5+ messages / 3 participants
[nested] [flat]
* Another constant in foreign key problem.
@ 2012-02-22 10:35 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2012-02-22 10:35 UTC (permalink / raw)
To: pgsql-sql
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.
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'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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another constant in foreign key problem.
@ 2012-02-22 11:18 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-02-22 11:18 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another constant in foreign key problem.
@ 2012-02-22 11:25 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2012-02-22 11:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another constant in foreign key problem.
@ 2012-02-22 12:04 Philip Couling <phil@pedal.me.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 5+ messages in thread
From: Philip Couling @ 2012-02-22 12:04 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Hi Gary
In short you can't have a foreign key here because foreign keys are just
that: a *key* from another table. fl_level is not a key, it is not
unique and requires fl_f_id to be unique.
If you want a foreign key between these two tables then you must add the
facility id to the document library and use a composite key (fl_f_id ,
fl_level) as the foreign key. This may be advantageous as the same
structure could be re-used across other facilities (even though it would
not be applicable to all).
There are a couple of alternatives to this. One is to add a separate
column to facility_levels. The sole purpose of this column would be to
proved a unique key on every row in the table. Foreign keys (such as on
library_document_user_level) would reference this and not fl_level.
There would be no constraint here to prevent a row in
library_document_user_level from referencing the wrong facility.
Another possible alternative which is very PostgreSQL specific is to use
inheritance. Create a child table storing only facility_levels for
fl_f_id 22. The foreign key would reference the child table and not
facility_levels. Everything in the child table would also exist in
facility_levels. As this is so specific to PostgreSQL and is not hugely
common as a technique, read this as a *possible* solution not a
recommended one.
Hope this helps
On 22/02/2012 10:35, Gary Stainburn wrote:
> 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.
>
> 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've googled this but can't find a suitable solution. Can anyone help please.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another constant in foreign key problem.
@ 2012-02-22 12:19 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Philip Couling <phil@pedal.me.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2012-02-22 12:19 UTC (permalink / raw)
To: pgsql-sql
Thank you to you both. I was thinking that I wasn't going to be able to do
this.
As the use of these tables is purely to control the web interface to this
database I have decided control this via the application rather than within
the database. It will require less coding and is simpler.
On Wednesday 22 February 2012 12:04:41 Philip Couling wrote:
> Hi Gary
>
> In short you can't have a foreign key here because foreign keys are just
> that: a *key* from another table. fl_level is not a key, it is not
> unique and requires fl_f_id to be unique.
>
> If you want a foreign key between these two tables then you must add the
> facility id to the document library and use a composite key (fl_f_id ,
> fl_level) as the foreign key. This may be advantageous as the same
> structure could be re-used across other facilities (even though it would
> not be applicable to all).
>
>
>
> There are a couple of alternatives to this. One is to add a separate
> column to facility_levels. The sole purpose of this column would be to
> proved a unique key on every row in the table. Foreign keys (such as on
> library_document_user_level) would reference this and not fl_level.
> There would be no constraint here to prevent a row in
> library_document_user_level from referencing the wrong facility.
>
>
> Another possible alternative which is very PostgreSQL specific is to use
> inheritance. Create a child table storing only facility_levels for
> fl_f_id 22. The foreign key would reference the child table and not
> facility_levels. Everything in the child table would also exist in
> facility_levels. As this is so specific to PostgreSQL and is not hugely
> common as a technique, read this as a *possible* solution not a
> recommended one.
>
> Hope this helps
>
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-02-22 12:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-22 10:35 Another constant in foreign key problem. Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-02-22 11:18 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-02-22 11:25 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-02-22 12:04 ` Philip Couling <phil@pedal.me.uk>
2012-02-22 12:19 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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