Optimal DB structure for a multi-sport website?

La_Parka

I'm making a PHP-MySQL site that provides injury and transaction updates on athletes from major professional sports such as baseball, football, basketball, and hockey. For purposes of query speed, ease of coding, and flexibility should major changes to the site need to be made, which of the two following database structures would be best?

Structure A - A separate database for the players and teams of each sport

(Primary keys are underlined)

baseball_players
playerid
teamid
fname
lname

football_players
playerid
teamid
fname
lname

^(and so on, for basketball and hockey)

baseball_teams
teamid
location
nickname

football_teams
teamid
location
nickname

^(and so on, for basketball and hockey)

Structure B - All sports use the same tables

players
playerid
teamid
fname
lname

teams
teamid
sportid
location
nickname

sports
sportid
sportname

leatherback

I'd choose structure B since whenever a new sport is addde to the site, you can just continue working with the current setup.

Just get your menu's dynamically from the database (select distinct sportid, sportname from sport...) and you buidl a site which you can expand without hrouble.

superwormy

Definitally structure B, structure A is AWEFUL for performance, annoyance of coding, and expandability reasons.

I also go with just ID for each table instead of teamid, or playerid, because then I have to do:

SELECT team.teamID FROM ...

Which looks redundent as opposed to:

SELECT team.ID FROM...

See my point?

leatherback

Well.. a thought on using unique colum names..

select t_id from teams

vss

select team.id from teams

So there is something to say for both table constructs. I personally always try to use unique columnames, to avoid having to place table names onto every colum I refer to.