pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRelation in tables
4+ messages / 3 participants
[nested] [flat]
* Relation in tables
@ 2005-02-16 16:43 lucas@presserv.org
2005-02-16 18:56 ` Re: Relation in tables PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 4+ messages in thread
From: lucas@presserv.org @ 2005-02-16 16:43 UTC (permalink / raw)
To: pgsql-sql
Hello all...
I am starting in Postgresql...
And I have a question:
I am developing a DB system to manage products, but the products may be
separated by departaments (with its respectives coluns)... Like:
CREATE TABLE products(
id serial primary key,
desc valchar(100),
...
);
Okay, but the products is typed by "amount departament" and this departament
should not have access to other coluns like "values, Money, etc...".
The "finances departament" may modify the data into products table, but this
departament should not have access to coluns like "amounts, etc...".
I' ve tried to create the products table with INHERITS but its not right...
look:
CREATE TABLE prod_amounts (
amount_min numeric,
amount_cur numeric,
amount_max numeric,
...
) INHERITS products;
CREATE TABLE prod_values (
buy_value money,
sen_value money,
...
) INHERITS products;
Okay, but the problem is: I can INSERT a prod_amounts normaly and automaticaly
the products table will be filled, but when i try to modify the data in
prod_amounts (references in products) there is no data....
I think its not right ( I am sure :/ ).
How Can I do it???
How Can I References Between Prod_amounts and Prod_Values automaticaly?
remembering the Amounts departament may not access the Values departament data
and the Values departament may not access the amounts data... And the products
will be registred (typed) in Amount departament....
Thanks for all.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Relation in tables
2005-02-16 16:43 Relation in tables lucas@presserv.org
@ 2005-02-16 18:56 ` PFC <lists@boutiquenumerique.com>
2005-02-17 10:57 ` Re: Relation in tables lucas@presserv.org
0 siblings, 1 reply; 4+ messages in thread
From: PFC @ 2005-02-16 18:56 UTC (permalink / raw)
To: lucas@presserv.org; pgsql-sql
> Hello all...
> I am starting in Postgresql...
> And I have a question:
> I am developing a DB system to manage products, but the products may be
> separated by departaments (with its respectives coluns)... Like:
>
> CREATE TABLE products(
> id serial primary key,
> desc valchar(100),
> ...
> );
>
> Okay, but the products is typed by "amount departament" and this
> departament
> should not have access to other coluns like "values, Money, etc...".
> The "finances departament" may modify the data into products table, but
> this
> departament should not have access to coluns like "amounts, etc...".
>
> I' ve tried to create the products table with INHERITS but its not
> right...
> look:
Use a view per department, which show/hide the columns according to your
liking. Give each department a schema and put everything related to it
inside for cleanliness. Use UPDATE triggers on the views, which in fact
write to the products table, so that the departments can only update the
columns you like. You can even make some columns readable but not
writeable, by raising an exception if a modification is attempted on that
column.
If you want to reuse your code between departments, you will want all the
views to have the same columns, so make them return NULL for the fields
that they cannot see.
Finally don't forget to make the products table inaccessible the
departments.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Relation in tables
2005-02-16 16:43 Relation in tables lucas@presserv.org
2005-02-16 18:56 ` Re: Relation in tables PFC <lists@boutiquenumerique.com>
@ 2005-02-17 10:57 ` lucas@presserv.org
0 siblings, 0 replies; 4+ messages in thread
From: lucas@presserv.org @ 2005-02-17 10:57 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: pgsql-sql
>
> Use a view per department, which show/hide the columns according to your
> liking. Give each department a schema and put everything related to it
> inside for cleanliness. Use UPDATE triggers on the views, which in fact
> write to the products table, so that the departments can only update the
> columns you like. You can even make some columns readable but not
> writeable, by raising an exception if a modification is attempted on that
> column.
>
> If you want to reuse your code between departments, you will want all the
> views to have the same columns, so make them return NULL for the fields
> that they cannot see.
>
> Finally don't forget to make the products table inaccessible the
> departments.
>
Okay, I will use Triggers to manage the insert and update table and viewers to
select records, but need I use more than one table (with inherits)? Or I just
use product table with the ALL departaments coluns and check the perms into
Triggers and Viewers???
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Relation in tables
@ 2005-02-16 18:33 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 4+ messages in thread
From: KÖPFERL Robert @ 2005-02-16 18:33 UTC (permalink / raw)
To: pgsql-sql
You may possibly solve the problem with the inheritted tables with the
RULE-System of pgsql. But this seems oversized to me.
You could rather create several tables, each with its matching
rights/privileges and 'connect' them via an 1:1 relation.
The 'real' way such thing is normally done is to write stored procedures
which are accessible (Executable) only by those departments that may. If you
give these procs SECURITY DEFINER, you can restrict access to the actual
tables to only the procedure's owner
> -----Original Message-----
> From: lucas@presserv.org [mailto:lucas@presserv.org]
> Sent: Mittwoch, 16. Februar 2005 17:43
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Relation in tables
>
>
> Hello all...
> I am starting in Postgresql...
> And I have a question:
> I am developing a DB system to manage products, but the
> products may be
> separated by departaments (with its respectives coluns)... Like:
>
> CREATE TABLE products(
> id serial primary key,
> desc valchar(100),
> ...
> );
>
> Okay, but the products is typed by "amount departament" and
> this departament
> should not have access to other coluns like "values, Money, etc...".
> The "finances departament" may modify the data into products
> table, but this
> departament should not have access to coluns like "amounts, etc...".
>
> I' ve tried to create the products table with INHERITS but
> its not right...
> look:
>
> CREATE TABLE prod_amounts (
> amount_min numeric,
> amount_cur numeric,
> amount_max numeric,
> ...
> ) INHERITS products;
>
> CREATE TABLE prod_values (
> buy_value money,
> sen_value money,
> ...
> ) INHERITS products;
>
> Okay, but the problem is: I can INSERT a prod_amounts normaly
> and automaticaly
> the products table will be filled, but when i try to modify
> the data in
> prod_amounts (references in products) there is no data....
> I think its not right ( I am sure :/ ).
> How Can I do it???
> How Can I References Between Prod_amounts and Prod_Values
> automaticaly?
> remembering the Amounts departament may not access the Values
> departament data
> and the Values departament may not access the amounts data...
> And the products
> will be registred (typed) in Amount departament....
>
> Thanks for all.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
> joining column's datatypes do not match
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-02-17 10:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-16 16:43 Relation in tables lucas@presserv.org
2005-02-16 18:56 ` PFC <lists@boutiquenumerique.com>
2005-02-17 10:57 ` lucas@presserv.org
2005-02-16 18:33 Re: Relation in tables KÖPFERL Robert <robert.koepferl@sonorys.at>
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