From: eugene kim <eugene1977@hotmail.com>
Newsgroups: comp.databases.oracle.server
Subject: data modeling question.. too many joins?
Date: Mon, 09 Sep 2002 12:47:52 +0000
Organization: Mailgate.ORG Server - http://www.Mailgate.ORG
Lines: 65
Sender: yoyoeugenekim@hotmail.com
Message-ID: <alhbth$pvg$1@newsreader.mailgate.org>
NNTP-Posting-Host: cs24162112-122.hot.rr.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
X-Trace: newsreader.mailgate.org 1031549682 26608 24.162.112.122 (9 Sep 2002 05:34:42 GMT)
X-Complaints-To: abuse@mailgate.org
NNTP-Posting-Date: Mon, 9 Sep 2002 05:34:42 +0000 (UTC)
User-Agent: KNode/0.7.1


school has teams,
two teams play games

if i want to find out scores of games that my school played..
i had to join each table twice..

since i heard join is expensive process, i 'm wondering if it's better to 
have school name in teams table(duplication of data)
for faster data retrieval..


create table schools (
        school_id integer primary key,
        name varchar(100) unique,
        location varchar(100)
);

create table teams(
        team_id integer primary key,
        school_id integer references schools,
        sport varchar(100),
        gender char
);

create table games (
        game_id integer primary key,
        home_team_id integer references teams,
        home_points integer,
        visitor_team_id integer references teams,
        visitor_points integer,
        game_date date,
        finished char default 'n'
);

-- output the results of 'my school' in football, showing scores of 
completed games

--my school home
select g.home_points baylor, s2.name opponent, g.visitor_points, g.game_date
from schools s, schools s2, teams t, teams t2, games g
where   s.name = 'my school' and
        s.school_id = t.school_id and
        g.home_team_id = t.team_id and
        t.sport = 'football' and
        t.gender = 'm' and
        g.finished = 'y' and
        s2.school_id = t2.school_id and
        g.visitor_team_id = t2.team_id;


--oponent home
select  s2.name opponent, g.home_points, g.visitor_points baylor, 
g.game_date
from schools s, schools s2, teams t, teams t2, games g
where   s.name = 'my school' and
        s.school_id = t.school_id and
        g.visitor_team_id = t.team_id and
        t.sport = 'football' and
        t.gender = 'm' and
        g.finished = 'y' and
        s2.school_id = t2.school_id and
        g.home_team_id = t2.team_id;

if i should have asked this question somewhere else, pls tell me to do so..
thank you