pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

[GENERAL] Schema design / joins
2+ messages / 2 participants
[nested] [flat]

* [GENERAL] Schema design / joins
@ 2010-05-03 04:52  E H <edgeofmyhand@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: E H @ 2010-05-03 04:52 UTC (permalink / raw)
  To: pgsql-sql

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [GENERAL] Schema design / joins
@ 2010-05-04 12:05  silly sad <sad@bankir.ru>
  parent: E H <edgeofmyhand@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: silly sad @ 2010-05-04 12:05 UTC (permalink / raw)
  To: pgsql-sql

> __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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2010-05-04 12:05 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-03 04:52 [GENERAL] Schema design / joins E H <edgeofmyhand@gmail.com>
2010-05-04 12:05 ` silly sad <sad@bankir.ru>

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