Message-ID: <43624661.7000404@brandywine.com>
Date: Fri, 28 Oct 2005 11:40:17 -0400
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>
In-Reply-To: <20051028105908.onaegu44ynnog8os@www.presserv.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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