Message-ID: <4366471F.1020509@brandywine.com>
Date: Mon, 31 Oct 2005 11:32:31 -0500
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: Re: Referencing
References: <20051028105908.onaegu44ynnog8os@www.presserv.org>
	<43624661.7000404@brandywine.com>
	<20051028130720.jxoth8izb468k44s@www.presserv.org>
In-Reply-To: <20051028130720.jxoth8izb468k44s@www.presserv.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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