Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 9B515634917
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  4 May 2010 09:07:23 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 84232-01-9 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  4 May 2010 12:07:16 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moon.antora.ru (moon.antora.ru [89.111.189.180])
	by mail.postgresql.org (Postfix) with ESMTP id 55BD2634E10
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2010 09:05:13 -0300 (ADT)
Envelope-to: pgsql-sql@postgresql.org
Received: from moon.inner.space ([10.1.1.5] helo=moon.antora.ru)
	by moon.antora.ru with esmtp (Exim 4.67)
	(envelope-from <sad@bankir.ru>) id 1O9Gs9-0000Ij-Af
	for pgsql-sql@postgresql.org; Tue, 04 May 2010 16:05:09 +0400
Received: from [192.168.1.6] [94.233.219.244] by moon.antora.ru (Axigen)
	with (DHE-RSA-AES256-SHA encrypted) ESMTPSA id 0D2B5D;
	Tue, 4 May 2010 16:05:09 +0400
Message-ID: <4BE00D75.30204@bankir.ru>
Date: Tue, 04 May 2010 16:05:09 +0400
From: silly sad <sad@bankir.ru>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.5)
	Gecko/20100111 Thunderbird/3.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: [GENERAL] Schema design / joins
References: <7027958f-bbe6-4571-9659-71dc4b317344@e2g2000yqn.googlegroups.com>
In-Reply-To: 
 <7027958f-bbe6-4571-9659-71dc4b317344@e2g2000yqn.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201005/12
X-Sequence-Number: 34060

> __Orgs__
>   id
>   name
>
> __Seasons__
>   id
>   org_id  fk(orgs.id)
>   name
>
> __Teams__
>   id
>   season_id  fk(seasons.id)
>   name
>
> __TeamFees__
>   id
>   team_id  fk(teams.id)
>   *org_id<--- (?put extra fk here to avoid many joins?)

NO.

instead of it
use triggers before insert/update

CREATE FUNCTION foo() RETURNS TRIGGER AS $$
BEGIN
   SELECT org_id INTO new.org_id FROM __seasons__ WHERE id=new.season_id;
END;
$$ LANGUAGE plpgsql;

et cetera.

AND now other way lead you to the future.