relationship problems

[MrBLT]

hello everyone
i've been working on my basketball database for a couple of months now. i'm pretty sure i've got the tables right. Just a few things before i tell you my tables.
This is just a pet project and it is based our the nbl(national league) and i'm just concentrating on the team stats and no player stats.

game
gameid(pk)
hometeam
awayteam
date
venueid
homeline(gambling term)
awayline

score
gameid(pk)
periodid
score

stats
gameid(pk)
teamid
fgm
fga
ftm
fta
3ptm
3pta
offreb
defreb
assist
steal
t/o
block
fouls

team
teamid(pk)
team

venue
venueid(pk)
venue

period
periodid(pk)
period

the thing is, i'm lost on relationship and how to join. would i be correct in saying that i have a one to many relationship for this database.
i'm lost on how you can have two pk cause i kept getting an error message but i think this had to do with the relationships.
can someone provide some help for my dilemma.

 

[neileg]

The gameid is only a PK in the game table. In the other tables it is a foreign key.

 

[MrBLT]

thanks neileg
may i ask what would be my primary key for the score and stats table then

 

[MrBLT]

does my relations look right or do i have some more work to do. i found that in my teams table when you expand it it shows the games table but it shows all fields except the home team. And also when there was an away game the expanded teams table did not show the data.
Thanks again

 

[neileg]

You don't have to have a primary key in any table. I'm afraid I have no formal db development training so I don't know what the 'correct' approach to this is. In my databases, if the funtionality doesn't demand a PK, I don't apply one.

 

[MrBLT]

sorry i sent the wrong attachment

 

[Pat Hartman]

Although Jet does not require that you define a primary key for every table, good practice dictates one. Without primary keys and defined relationships, you cannot enforce referential integrity. There are also situations where queries that join tables will not be updatable because Jet cannot determine the cardinality of a relationship because one or both tables do not have primary keys defined.

1. Why do you have stats linked three times to games?
2. Define pk's for the tables that do not have one. For stats, use GameID and TeamID. For score, use GameID, TeamID, PeriodID. If you have a table where you can't uniquely identify a row with a couple of columns, then add an autonumber and use that as a primary key.
3. Check the enforce RI checkbox.

 

[MrBLT]

thanks for your help Pat.
Does this look right now. However i'm still having trouble with the team table. When i expand a team for more details it shows the fields of the game table except for the home team. It also doesn't display any information either. Is this a problem or am i worrying over nothing.
Cheers Ben

 

[Pat Hartman]

If you are talking about clicking on the plus at the left of a row in the team table, the answer is that this can only link to one relationship. You'll need to create queries that join the tables that you want to see together.