DB Architecture : Linking to intersection or to main tables?

Question

Hi,

I'm creating fantasy football system on my website but i'm very confuse about how I should link some of my table.

Tables

The main table is Pool which have all the info about the ruling of the fantasy draft.

A standard table User, which contains the usual stuff.

Intersection table called pools_users which contains id,pool_id,user_id because a user could be in more than one pool, and a pool contains more than 1 user.

The problem

Table Selections => that's the table that is causing problem. That's the selection that the user choose for his pool. This is related to the Player table but thats not relevant for this problem.

Should I link this table to the table Pools_users or should I link it with both main table Pool and User. This table contains id,pool_id,user_id,player_id,...

What is the best way link my tables? When I want to retrieve my data, I normally want the information to be divided BY users. "This user have those selections, this one those selections, etc).

Answer 1

I believe I would link it with a foreign key to both pools and users (by pool_id and user_id respectively). A selection can only have one pool. A selection can only have one user.

I might use a primary key on the selections table that looks like: pool_id, user_id, selection_id so the 30th selection by user 123 in the pool 001 would be 001/123/30.

Would you normally go after the data by user or pool and user? Would you really print all the users selections regardless of pool? If so you might consider rearranging the primary key so that it is user_id, pool_id, and then selection_id. This could make lookups by user_id alone faster since it will be able to go after the first part of the primary key index.

Answer 2

What is a Pool? Is it a League, that is, a group of teams competing against each other? The reason I ask is because it seems like you have a entity missing, to wit, Team*()**.

To a certain extent this decision depends on the rules of the fantasy football game you are running, but in my experience most games allow Users to participate in multiple Leagues (?Pools?) but to manage just the one Team. So there is a one to one relationship between Users and Teams. Once we look at it this way it becomes clear that Selections must be an intersection table between Teams (Users) and Players.

If your particular set of rules mandates one Team per User then you should merge the two entities into a single table - which I would choose to call Managers, as that fits the domain better, but the decision is yours ;).