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 ))