Dataheads???

[VitoStallion]

I'm building the database and I've come to a fork in the road. I'm at odds. Here's the question:

Do I make separate "(team_ID)ROSTER" tables for each team and relate it to my TEAMS table which includes Team_ID, TeamName, TeamAbr, TeamLocation, TeamColorPri, TeamColorSec,TeamColorTer?


or

make a single PLAYERS table (indexed off of Team_ID) and do a LOOKUP table for roster queries?

I still haven't figured out where the free agents are dumped in HTML, but I was thinking we need to track them also (thoughts)

I'm figuring the latter is better though I wonder about DB performance as the entire PLAYERS table would have to queried versus a single roster table.

Thoughts?

[roadrash]

Re: Dataheads???

I'm building the database and I've come to a fork in the road. I'm at odds. Here's the question:

Do I make separate "(team_ID)ROSTER" tables for each team and relate it to my TEAMS table which includes Team_ID, TeamName, TeamAbr, TeamLocation, TeamColorPri, TeamColorSec,TeamColorTer?


or

make a single PLAYERS table (indexed off of Team_ID) and do a LOOKUP table for roster queries?

I still haven't figured out where the free agents are dumped in HTML, but I was thinking we need to track them also (thoughts)

I'm figuring the latter is better though I wonder about DB performance as the entire PLAYERS table would have to queried versus a single roster table.

Thoughts?

I did some experimenting with this a while back. I was taking a DB class and even asked my Prof . You basically up with a many-tomany-to-many-many relationship. It gets real ugly.

What I figured out is that if you want to keep lifetime stats for players, you have to have a

players_table(Player_ID, LName, FName, Misc_data, Team_ID)
team_table(Team_ID, Team_name, City, etc)

stats_table(Team_ID, Player_ID, Game_ID, Stats...)

Game_table(Game_ID, Week, Year, Team_ID_Home, Team_ID_Away, Score_home, Socre_away, PLayer_ID_MVP, etc...)

Just about everything except etc/misc is either a primary key or a foreign key, and the join statements for some of the queries are quite involved.

Does this help?

[VitoStallion]

a little

[Cringer]

I am getting the feeling this shit doesn't come to you through osmosis. sucks, i am going to have to do some boring ass reading I think....

[roadrash]

I guess to answer your question, you cannot index a player off team alone since one player could play for many teams. This would require three tables just to get a roster for a given time period.

You could do a roster_ID table to make pulling current rosters fast, but this is called indexing, just set it up as an index.

If you send me all the fields and queries you want tracked, and I could do the data modeling for ya.

[VitoStallion]

could I send you the database (it's in mySQL)

I want to be able to do the following with the frontend:

View a player's pic, stats, awards, injures, transactions (at least 3 seasons of history online)

View a team's logo, roster, stats, awards, injuires, transactions (at least 3 seasons of history online)

View game results like that on NFL.com (individual and game statistics) (current season online)

that's all

I need help with the modeling big time

I started some imports tonight and KABOOM the local access DB is nt liking our CSV dumps without some mods.