data modeling question.. too many joins?

From: eugene kim <eugene1977_at_hotmail.com>
Date: Mon, 09 Sep 2002 12:47:52 +0000
Message-ID: <alhbth$pvg$1@newsreader.mailgate.org>

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 Received on Mon Sep 09 2002 - 07:47:52 CDT