Thread: Fwd: Re: Referencing
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.... valuemoney ); 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.... valuemoney ); ---and--- create table financial( id serial primary key, cred_deb smallint, value money, references integer references ???, --<<-HEREIS 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 >
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_idinteger 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, -- referencessuppliers, 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 activitya 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 ))
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.
lucas@presserv.org wrote:> Quoting Daryl Richter <daryl@brandywine.com>:>>> It's hard to say without knowing more
preciselywhat 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
integerreferences 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 ))
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.
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