Can't figure out table structure and normalization

[Guest]

I've got a new project that I am just now starting. This database is going
to be a lot more complicated than my last. I'm attempting to build a Fantasy
Football database. Sounds simple right? Well I'm stuck already. I can't
figure out how my table structure and relationships should be setup. I'm
stuck on the following set of rules that I want my database to follow:

1. A single football player can only be a member of one team in a league.
2. But a single football player can be a member of many teams as long as
each team is a member of separate leagues.

So we're talking at least 3 tables, a teams table, a leagues tables and a
players table. But I think there's some Many to Many relationships there so
I'll probably need some junction tables. Is there anyone out there that can
help me satisfy those two rules? I think I'll probably be able to figure
everything else out from there.

Thanks a ton.

Jonathan

 

[Rick B]

There are tons of websites out there to manage fantasy football leagues.
Have you tried looking at those? It would probably save you many hours of
work and would offer all kinds of cool features. I think most of them let
your users have limited access and some of them update stats automatically.

 

[Guest]

Rick,

True there are a number of fantasy football leagues available out there.
However, let's suppose that there weren't, how would I create the proper
table structure and relationships to satisfy those two rules?

Jonathan

 

[Earl Lewis]

Jonathan,

You need that linking table you mentioned to satisfy your many-to-many relationship. Basically, its a TeamPlayers table with a primary key that would be a unique combination of PlayerID and TeamID. You don't have to fully enforce the business rules with the table structure itself. You can build some of that into the application, i.e. not allowing a team member in more than one team in the same league. The way I'm reading it, table structure alone will not solve this problem.

Earl
Rick,

True there are a number of fantasy football leagues available out there.
However, let's suppose that there weren't, how would I create the proper
table structure and relationships to satisfy those two rules?

Jonathan