help building a fantasy football database. What tables and info to build

[karljames]

Hey phpfreaks,

I really, really, really need some help with this.
I am attemping to make a NFL Fantasy Football Database,
for my website at theufl.com/index6.html
I really need advise on what tables to make.
So far I know I need a team/login table, players table, transactions table.
I know how to create them. But I just really need help on deciding
what tables i need. I attempted a flow chart but didnt like how it was
ending up. I have a written theory of what i want for my website.
You can view this at http://www.theufl.com/ufl_project.htm

Any help or even step by step on this would be great.
Please feel free to email me or message me on aim at kjames1973.

You all will be my hero if you can get this ball rolling
thanks, kdog!!!!

[karljames]

I really can't believe their has been several views and no replies.
Totally AMAZING!!!!

Karl

[GingerRobot]

Well an attitude like that isn't exacty going to get you helped, especially seeing as you aren't really asking for help to do with PHP. No-one but yourself can tell you how to go about this, it is a problem dependant on how YOU want it to work.

[athmpsn]

You should check out some of the independent game communities out there for help with your idea. As Ginger said, no one can really help with the programming choices, but the communities can help you with creating a design document which can go a long way towards helping with the programming :D

GL with it.

[Barand]

This forum is for help on specific PHP problems. What you are asking is a non-trivial data analysis exercise of your project. As such it would be better placed in the freelance section where you can engage the services of a db consultant and you can get quotes for the job.

But as a start, list the data items you need to store and group them by entity (team, player etc). Each data item should appear once only. Each entity should have a unique id. So you then have a list like
[pre]
team          player        etc
-----        -------
id            id
team_name    player_name
manager      position
              salary
[/pre]           
Look at the direct relationships between your entities. A chart like this can help.
[pre]
+---+
|  | team
+---+---+
|  | X | player
+---+---+---+
|  | X | X | trade
+---+---+---+---+
|  | X |  |  | team_stats
+---+---+---+---+---+
|  | X |  |  | ? | game
+---+---+---+---+---+
[/pre]

You also need to consider the nature of the relationship

[b]one-to-one[/b] : a player has one name, so name can go in the player table.

[b]one-to-many[/b] : one team has many players, a player plays for one team.  this case, put the id of team in the player table as a FOREIGN KEY.
             
[b]many-to-many [/b]: if you want a historical perspective, then we can use the above example here too. One team has many players and (in his career) one player has many teams. In this case you have to introduce a third entity containing team id and player id to link the two together. This may be an already identified entity, such as "trade" maybe.

Note in the above chart I have a "?" against team-stats/game. This would depend on whether you just wanted accumulated stats or hold them on a game by game basis for subsequent aggregation.

Salary has been placed in the player table but if you want a record of salaries over the players career then this would  have to be moved to another table and become o one-to-many relation.

As you can see, there are lots of if's and but's that require a full knowledge of the data, the relationships and processing requirements. This is going to be an iterative process requiring much discussion with the client and not something that is a clear-cut solution from your spec.

EDIT: This is a rough-cut approach, there would also be a normalisation excercise on the data.

[karljames]

First off to phpfreaks!
I am sorry, I am not trying to sound hostile in anyway.
I was just being basically a smart ass attitude, and I am
a happy, laid back type of person.

Second,
Thanks for the replies and leads in this matter.
Especially to barand.

Barand,
I some what understand where you are going in detail.
If I could aquire your help in more detail through forums
please tell me where to write it or if we can chat online.

I promise you I can catch on! I just need the leads.
The goal of the team pages is to show login successfull
Team ownership, Team management, Trophy Case, Current stats
that are prudent to the team and players and a few forms like
submit starting line up, qb,rb,rb,wr,wr,te,k,d (UT) which are
Football NFL positions then display them on another page called
starting lineups vs the team you are playing.
I can also show you a example of what i want to create is
at this page, http://www.webleaguemanager.com/demo/leaguehome.html
I want to get as close I can to those pages, especially look at the reports page,
and the other pages in that subject, along with a if you click on a owners page
you will see what i mean, actually manage team page. I also want to apply a fourm
as well. I only want the whole ball of wax!!! :-)

looking forward to speaking with you again.
thanks, Karl