Re: Fwd: Re: Referencing - Mailing list pgsql-sql

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