From: E H <edgeofmyhand@gmail.com>
To: pgsql-sql@postgresql.org
Subject: [GENERAL] Schema design / joins
Date: Sun, 2 May 2010 21:52:17 -0700 (PDT)
Message-ID: <7027958f-bbe6-4571-9659-71dc4b317344@e2g2000yqn.googlegroups.com> (raw)
Hi,
This is more of a general schema design, any advice is much
appreciated.
I have a Organization table. Nearly every other table in the schema
is related to this Org table in some way. So, some tables may be 3 or
4 tables 'away' from the Org table. In order to filter by the org_id,
I need to join a bunch(?3-6) of tables
Simple example below, TeamFees belong to a Team, which belongs to a
Season, which belong to an Org. In order to get all the TeamFees that
belong to a given Org, I need to join all the tables which isn't a big
deal, but I'm just wonder if putting an extra 'org_id' on Team fees
would help anything...
** Is it a bad idea to put an extra FK 'org_id' on the TeamFees table
to avoid all the joins?
** What about putting an 'org_id' on every table? (it seems somewhat
redundant/unnecessary to me)
I've never had any formal education in rdbms, but from what I can
gather, foreign keys are meant to ensure data consistency, not reduce
the number of joins required. Although, it sure seams like it would
simplify the queries if I stuck extra 'org_id' columns in certain
places. I don't have any particular reason that I'm trying to avoid
joins -- I'm just wondering if there is something simpler or if 'thats
just how it is.'
I would really, really appreciate any suggestions from folks with
rdbms schema design experience! Thanks!
__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?)
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: edgeofmyhand@gmail.com
Subject: Re: [GENERAL] Schema design / joins
In-Reply-To: <7027958f-bbe6-4571-9659-71dc4b317344@e2g2000yqn.googlegroups.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