From: Daryl Richter <daryl@brandywine.com>
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: Re: Referencing
Date: Fri, 28 Oct 2005 11:40:17 -0400
Message-ID: <43624661.7000404@brandywine.com> (raw)
In-Reply-To: <20051028105908.onaegu44ynnog8os@www.presserv.org>
References: <20051028105908.onaegu44ynnog8os@www.presserv.org>
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 ))
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: daryl@brandywine.com, lucas@presserv.org
Subject: Re: Fwd: Re: Referencing
In-Reply-To: <43624661.7000404@brandywine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox