Re: Fwd: Re: Referencing

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