[Resolved] Poor DB design?

La_Parka

I'm trying to make a basketball statistics database display which player belonged to which team in a given year, but can't figure out a JOIN query that will be able to show various fields from the DB in this manner on a PHP page:

Year || Player || Team || Team Wins || Team Losses
1984-85 || Michael Jordan || Bulls || 38 || 44

Is it overkill to have a primary key and separate table for the actual years in which the seasons occur, as I have in the diagram, or is efficient DB design?

bastien

be simpler to add PLAYERID to the records table and then the join is simple...

ahundiak

La Parka,
Your design is fine. What sort of info are you having trouble extracting?

La_Parka

Originally posted by bastien
be simpler to add PLAYERID to the records table and then the join is simple...

The problem with that is that there will be multiple players on each team.

bastien

but year and player id make it distinct

"select a.player_name,
b.player_id,
b.season,
b.games_won,
b.games_lost
from
player_table a,
records b
where
a.player_id=b.player_id
and a.player_id = $player"

where $player is the value passed from the form or link or whatever...

La_Parka

Originally posted by ahundiak
La Parka,
Your design is fine. What sort of info are you having trouble extracting?

I'm having trouble getting data as pictured in the attachment for this post.

I've tried queries like "SELECT players.playerid, fname, lname, teams.teamid, teamname, wins, losses FROM teams
INNER JOIN records USING (teamid)
INNER JOIN playerlookup USING (recordid)
INNER JOIN players USING (playerid)"

I'm not sure if I can work in the relation to the "seasons" table with the way the database is presently constituted.

ahundiak

Pretty sure you want to use LEFT JOIN here and to start with the records table

SELECT whatever FROM records
LEFT JOIN teams USING(teamid)
LEFT JOIN seasons USING (seasonid)
LEFT JOIN playerlookup USING (recordid)
LEFT JOIN players USING (playerid)

You may need to use ON for some of the join conditions. And you will get one record for each record/player combination.