pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReferencing
7+ messages / 2 participants
[nested] [flat]
* Referencing
@ 2005-10-27 20:24 lucas@presserv.org
0 siblings, 0 replies; 7+ messages in thread
From: lucas@presserv.org @ 2005-10-27 20:24 UTC (permalink / raw)
To: pgsql-sql
Hi.
Is there a way to references dynamic tables? I.E:
I have a table called "buy" that create some records in "financial" table, but
there is other table called "send" that create other records in "financial".
"Financial" table have the moneys' movements and needs to be referenciable by
"buy or send". IE:
create table buy (
id serial primary key,
product_id integer,--references....
value money
);
create table send (
id serial primary key,
product_id integer, --references...
value money
);
create table financial(
id serial primary key,
cred_deb smallint,
value money,
references integer, --<<-HERE IS THE PROBLEM, it will reference to buy OR send
table
);
Well, I dont know if I was clean.
Thank you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Fwd: Re: Referencing
@ 2005-10-28 13:59 lucas@presserv.org
0 siblings, 1 reply; 7+ messages in thread
From: lucas@presserv.org @ 2005-10-28 13:59 UTC (permalink / raw)
To: pgsql-sql
Ok,
But the problem is becouse the "buy" and "send" tables referencing with other
father table, wich is different.
I shoud not create a spent table to put the "buy" and "send" values
becouse the
entire database is more complex than it. look:
create table output(
id serial primary key,
client integer references clientes,
fiscal_number varchar(30),
print_date date,
...
);
create table SEND(
id serial primary key,
output integer references input,
product_id integer,--references....
value money
);
create table input(
id serial primary key,
supplier integer references suppliers,
employee varchar(30),
...
);
create table BUY(
id serial primary key,
input integer references input,
product_id integer,--references....
value money
);
---and---
create table financial(
id serial primary key,
cred_deb smallint,
value money,
references integer references ???, --<<-HERE IS THE PROBLEM, it will
reference
to buy OR send table
);
How looked, the "buy" and the "send" table is identical except the father
references (INPUT or OUTPUT)... Then I shoud not create ONE table (spent) wich
has these informations.
And now my question: Is there a way to references (financial) with two
diferents
tables in the some row? Or need I create two diferents rows???
Thanks. (sorry for my english).
Quoting William Leite Araújo <william.bh@gmail.com>:
> Maybe you need is a table "spent" that has all fields of
> buy/send and one more, a flag to say is the field is a "buy" or a
> "send".
>
>
> 2005/10/27, lucas@presserv.org <lucas@presserv.org>:
>> Hi.
>> Is there a way to references dynamic tables? I.E:
>> I have a table called "buy" that create some records in "financial"
>> table, but
>> there is other table called "send" that create other records in "financial".
>> "Financial" table have the moneys' movements and needs to be
>> referenciable by
>> "buy or send". IE:
>> create table buy (
>> id serial primary key,
>> product_id integer,--references....
>> value money
>> );
>> create table send (
>> id serial primary key,
>> product_id integer, --references...
>> value money
>> );
>> create table financial(
>> id serial primary key,
>> cred_deb smallint,
>> value money,
>> references integer, --<<-HERE IS THE PROBLEM, it will reference to
>> buy OR send
>> table
>> );
>> Well, I dont know if I was clean.
>> Thank you.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>>
>
>
> --
> William Leite Araújo
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Re: Referencing
@ 2005-10-28 15:40 Daryl Richter <daryl@brandywine.com>
parent: lucas@presserv.org
0 siblings, 1 reply; 7+ messages in thread
From: Daryl Richter @ 2005-10-28 15:40 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
lucas@presserv.org wrote:
> Ok,
> But the problem is becouse the "buy" and "send" tables referencing with other
> father table, wich is different.
> I shoud not create a spent table to put the "buy" and "send" values
> becouse the
> entire database is more complex than it. look:
>
> create table output(
> id serial primary key,
> client integer references clientes,
> fiscal_number varchar(30),
> print_date date,
> ...
> );
> create table SEND(
> id serial primary key,
> output integer references input,
> product_id integer,--references....
> value money
> );
> create table input(
> id serial primary key,
> supplier integer references suppliers,
> employee varchar(30),
> ...
> );
> create table BUY(
> id serial primary key,
> input integer references input,
> product_id integer,--references....
> value money
> );
>
> ---and---
>
> create table financial(
> id serial primary key,
> cred_deb smallint,
> value money,
> references integer references ???, --<<-HERE IS THE PROBLEM, it will
> reference
> to buy OR send table
> );
>
> How looked, the "buy" and the "send" table is identical except the father
> references (INPUT or OUTPUT)... Then I shoud not create ONE table (spent) wich
> has these informations.
> And now my question: Is there a way to references (financial) with two
> diferents
> tables in the some row? Or need I create two diferents rows???
>
> Thanks. (sorry for my english).
>
It's hard to say without knowing more precisely what you are trying to
model, but I think this push you in the right direction:
-- This table takes the place of both SEND and BUY
create table activity(
id serial primary key,
product_id integer, --references....
value money
);
create table financial(
id serial primary key,
cred_deb smallint,
value money,
activity_id integer references activity
);
create table output(
id serial primary key,
client integer, --references clientes,
fiscal_number varchar(30),
print_date date,
activity_id integer references activity
);
create table input(
id serial primary key,
supplier integer, -- references suppliers,
employee varchar(30),
activity_id integer references activity
);
And then you do the following:
create view buy
as
select
a.id,
b.id as "input_id",
a.product_id,
a.value
from
activity a
join input b on b.activity_id = a.id;
The SELL view is left as an exercise for the reader.
--
Daryl Richter
Platform Author & Director of Technology
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Re: Referencing
@ 2005-10-28 16:07 lucas@presserv.org
parent: Daryl Richter <daryl@brandywine.com>
0 siblings, 1 reply; 7+ messages in thread
From: lucas@presserv.org @ 2005-10-28 16:07 UTC (permalink / raw)
To: daryl@brandywine.com; +Cc: pgsql-sql
Quoting Daryl Richter <daryl@brandywine.com>:
> It's hard to say without knowing more precisely what you are trying
> to model, but I think this push you in the right direction:
>
> -- This table takes the place of both SEND and BUY
> create table activity(
> id serial primary key,
> product_id integer, --references....
> value money
> );
>
> create table financial(
> id serial primary key,
> cred_deb smallint,
> value money,
> activity_id integer references activity
> );
>
> create table output(
> id serial primary key,
> client integer, --references clientes,
> fiscal_number varchar(30),
> print_date date,
> activity_id integer references activity
> );
>
> create table input(
> id serial primary key,
> supplier integer, -- references suppliers,
> employee varchar(30),
> activity_id integer references activity
> );
>
> And then you do the following:
>
> create view buy
> as
> select
> a.id,
> b.id as "input_id",
> a.product_id,
> a.value
> from
> activity a
> join input b on b.activity_id = a.id;
>
Okay, but references between (output/input) and ACTIVITY tables is 1 to N.
OUTPUT/INPUT - 1
to
ACTIVITY - N.
And not N to 1 how the example.
Then the reference field need to be on "ACTIVITY (send/buy)" table.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Re: Referencing
@ 2005-10-31 16:32 Daryl Richter <daryl@brandywine.com>
parent: lucas@presserv.org
0 siblings, 1 reply; 7+ messages in thread
From: Daryl Richter @ 2005-10-31 16:32 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
lucas@presserv.org wrote:
> Quoting Daryl Richter <daryl@brandywine.com>:
>
>> It's hard to say without knowing more precisely what you are trying to
>> model, but I think this push you in the right direction:
>>
[snipped old schema]
>
> Okay, but references between (output/input) and ACTIVITY tables is 1
to N.
> OUTPUT/INPUT - 1
> to
> ACTIVITY - N.
> And not N to 1 how the example.
> Then the reference field need to be on "ACTIVITY (send/buy)" table.
>
Ahh, ok. In that case I reverse it like so:
-- This table hold everything in common for inputs/outputs
create table transfer(
id serial primary key
);
create table output(
transfer_id int primary key references transfer,
client integer, --references clientes,
fiscal_number varchar(30),
print_date date
);
create table input(
transfer_id int primary key references transfer,
supplier integer, -- references suppliers,
employee varchar(30)
);
create table activity(
id serial primary key,
transfer_id int references transfer,
product_id integer, --references....
value money
);
create table financial(
id serial primary key,
cred_deb smallint,
value money,
activity_id integer references activity
);
create view buy
as
select
a.id as "input_id", -- or buy_id, etc. ...
b.supplier,
b.employee,
c.id as "activity_id",
c.product_id,
c.value
from
transfer a
join input b on b.transfer_id = a.id
join activity c on c.transfer_id = a.id;
If this is still not what you're after and you would like additional
assistance, it would really help to have at least a few rows of sample
data. Good luck!
--
Daryl Richter
Platform Author & Director of Technology
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Re: Referencing
@ 2005-10-31 19:58 lucas@presserv.org
parent: Daryl Richter <daryl@brandywine.com>
0 siblings, 1 reply; 7+ messages in thread
From: lucas@presserv.org @ 2005-10-31 19:58 UTC (permalink / raw)
To: daryl@brandywine.com; +Cc: pgsql-sql
Quoting Daryl Richter <daryl@brandywine.com>:
> lucas@presserv.org wrote:
> > Quoting Daryl Richter <daryl@brandywine.com>:
> >> It's hard to say without knowing more precisely what you are trying to
> >> model, but I think this push you in the right direction:
> >>
> > Okay, but references between (output/input) and ACTIVITY tables is 1 to N.
> > OUTPUT/INPUT - 1
> > to
> > ACTIVITY - N.
> > And not N to 1 how the example.
> > Then the reference field need to be on "ACTIVITY (send/buy)" table.
> >
>
> Ahh, ok. In that case I reverse it like so:
>
> -- This table hold everything in common for inputs/outputs
> create table transfer(
> id serial primary key
> );
Yes, I think it was what I wanted.
And how I check if a register in "Transfer" table is only referenciable by ONE
table (OR "output" OR "input")?? Would I create a Trigger like:
CREATE or REPLACE FUNCTION TG_output_check() RETURNS TRIGGER AS
$$
BEGIN
IF exists (select 1 from input where transfer_id=NEW.transfer_id) THEN
Raise Exception 'This activity (transfer) is alread setted to INPUT';
END IF;
RETURN NEW;
END;
$$ language 'plpgsql'; CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE
on OUTPUT EXECUTE PROCEDURE TG_output_check();
CREATE or REP...--- and the some function to INPUT ---
Or is there another way to check it?
Thank you again.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Re: Referencing
@ 2005-11-01 14:45 Daryl Richter <daryl@brandywine.com>
parent: lucas@presserv.org
0 siblings, 0 replies; 7+ messages in thread
From: Daryl Richter @ 2005-11-01 14:45 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
lucas@presserv.org wrote:
> Quoting Daryl Richter <daryl@brandywine.com>:
>
>> lucas@presserv.org wrote:
>> > Quoting Daryl Richter <daryl@brandywine.com>:
>> >> It's hard to say without knowing more precisely what you are trying to
>> >> model, but I think this push you in the right direction:
>> >>
>> > Okay, but references between (output/input) and ACTIVITY tables is 1
>> to N.
>> > OUTPUT/INPUT - 1
>> > to
>> > ACTIVITY - N.
>> > And not N to 1 how the example.
>> > Then the reference field need to be on "ACTIVITY (send/buy)" table.
>> >
>>
>> Ahh, ok. In that case I reverse it like so:
>>
>> -- This table hold everything in common for inputs/outputs
>> create table transfer(
>> id serial primary key
>> );
>
>
> Yes, I think it was what I wanted.
> And how I check if a register in "Transfer" table is only referenciable
> by ONE
> table (OR "output" OR "input")?? Would I create a Trigger like:
> CREATE or REPLACE FUNCTION TG_output_check() RETURNS TRIGGER AS
> $$
> BEGIN
> IF exists (select 1 from input where transfer_id=NEW.transfer_id) THEN
> Raise Exception 'This activity (transfer) is alread setted to INPUT';
> END IF;
> RETURN NEW;
> END;
> $$ language 'plpgsql'; CREATE TRIGGER TG_output_check BEFORE INSERT or
> UPDATE
> on OUTPUT EXECUTE PROCEDURE TG_output_check();
> CREATE or REP...--- and the some function to INPUT ---
>
> Or is there another way to check it?
>
> Thank you again.
>
Exactly, except for the small change that your trigger declaration needs
"for each row" as shown below:
CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE on output
for each row EXECUTE PROCEDURE TG_output_check();
--
Daryl
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-11-01 14:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-27 20:24 Referencing lucas@presserv.org
2005-10-28 13:59 Fwd: Re: Referencing lucas@presserv.org
2005-10-28 15:40 ` Daryl Richter <daryl@brandywine.com>
2005-10-28 16:07 ` lucas@presserv.org
2005-10-31 16:32 ` Daryl Richter <daryl@brandywine.com>
2005-10-31 19:58 ` lucas@presserv.org
2005-11-01 14:45 ` Daryl Richter <daryl@brandywine.com>
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