Been Stuck for Days Help needed!

[Smokeeye123]

Hello everyone. Im taking an entry level MIS class and this is killing me. Little bit of background, I have to develop a sports league database. Everything was going great until I hit this snag. I need to connect my 'PLAYERS' and 'GAME' table with 'PLAY' between. This is what it looks like http://i179.photobucket.com/albums/w...psdgokimca.png
The problem is each player plays in multiple games. If I listed the players in table 'PLAY' how could I possibly add a corresponding column from my 'GAME' table.



Please, please, please, help, Im so desperate. :I

[ItsMe]

I don't have the opportunity to look at your diagram you linked to, but ...

Sounds like you are going to need some junction tables. So, create a table that has two columns, place a primary key value from Table A in one of the columns and, in the other column, a primary key from Table B. In your junction table, you can have duplicates 'till the cows come home.

[Smokeeye123]

I don't have the opportunity to look at your diagram you linked to, but ...

Sounds like you are going to need some junction tables. So, create a table that has two columns, place a primary key value from Table A in one of the columns and, in the other column, a primary key from Table B. In your junction table, you can have duplicates 'till the cows come home.

I dont even know man...Im trying to link playerID with GameID in the same table but every game has 10 players total, 5 on each team. Impossible. My professor approved the ER Diagram but something must be wrong.

[ItsMe]

I really have no idea where you are struggling. When posting in this forum, you can use the Go Advanced option to Manage Attachments and upload them.

One thing that you might want to keep in mind is that building a database based on existing statistics is a sure fire way to make your eyes bleed. Statistics are what relational databases spit out. You do not feed statistics into a relational database. When defining your entities and diagraming relations, think of it using business rules. There is a list of teams, there is a list of leagues, there is a list of venues, players get hired and fired, players get injured, player rosters are generated before a game starts, not every player in the roster actually plays, game schedules/venues can be changed, etc.

Look at it from the perspective that the game has not started yet.

[ssanfu]

First thing: Don't use spaces in object names. It will only cause you headaches.

The problem is each player plays in multiple games. If I listed the players in table 'PLAY' how could I possibly add a corresponding column from my 'GAME' table.

I was going to suggest removing Play_tbl, but I just figured it out. You might have, say, 15 players on a team, but only 9 actually play (disregarding substitutions - right??)

You would need a junction table. To change Play_tbl to a junction table, you would need to add a Game_tbl FK field. And maybe the TeamID_FK.

PlaysGames_tbl
----------------------
PlaysGamesID_PK (autonumber)
PlayerID_FK
TeamID_FK
GamePosition (??? position for this game)

I don't know if the Position field should be in the Players_tbl. A player could be the SS in one game and 2B in another game???
Hmmm, have to think on this more.... Maybe have a GamePosition field in PlaysGames_tbl.....
Attachment 22940
If you add a TeamsPlayers_tbl (junction table), it would look something like

TeamsPlayers_tbl
------------------
TeamsPlayersID_PK (autonumber)
TeamID_FK
PlayerID_FK
PlayerNumber (can change for each season??)
NormalPosition (can change for each season??)
Season (date or year or Fall 2015 or ...)

Remove PlayerNumber, Position and TeamID from Players_tbl.

Same for Coaches:

CoachesTeams_tbl
----------------------
CoachesTeamsID_PK (autonumber)
CoachID_FK
TeamID_FK
Season

Remove CoachID from Teams_tbl.

================================================== ======

Warning..... I might have my head up a dark place and gasping for air.
My advise is worth what you paid for it.

[Smokeeye123]

Thank you guys so much, I was about to cry because my brain was bleeding from confusion. THANK YOU THANK YOU THANK YOU

[orange]

You may also get some ideas from this material and data model.