Beginner at relationships.

[Juice118]

Hey guys, first time poster in these forums.



I have decided to learn Access, for no good reason other than I am sure it will come in handy one day especially at work. I thought to get things started I would start a local sports tipping database for a bit of competition amongst the family. I have started on the design of the database but am a little stuck on how to handle relationships. I think my question is that basic or I am that far off track that most tutorials or readings assume you'd know it, so I am turning to you.

I have a table for the fixture of the local sport. Lets say Football. It has fields: MatchId (AutoNo.), RoundNo (No.), MatchDate (date), HomeTeam (No.), AwayTeam (No.), Location (Text), Winner (No.).

I have another table for the teams that has: TeamID (AutoNo.), TeamName (Text).

I have having trouble getting my head around the realtionship between the two tables for the home team and away team.

My question is: Should I have a seperate table for HomeTeamIDs and AwayTeamIds stemming from Teams table or should the realtionship look like this?



No example I have seen shows multiple relationships to the one table from the one table (ie creating the "Team_1" shown above for relationship purposes) and therefore I assuming that this is poor design?

Thanks.

[June7]

Design is okay, maybe not 100% normalized but probably workable. Make the jointype of each relationship: "Show all records from Fixture and only those from ..."

[jzwp11]

Welcome to the forum!

Since you have 2 teams per match or round, you have a one-to-many relationship which by normalization rules require a separate, but related table. The teams should be 2 records not 2 fields in that table. Additionally, I assume that you have several matches per sport, so another one-to-many relationship

tblSports (1 record for each sport you intend to track)
-pkSportID primary key, autonumber
-txtSport

tblSportMatches
-pkSportMatchID primary key, autonumber
-fkSportID foreign key relating to tblSports
-dteMatch

I'm not sure what you mean by round, but if there are multiple rounds per match, you have another one-to-many relationship.

tblSportMatchRound
-pkSpMatRdID primary key, autonumber
-fkSportMatchID foreign key to tblSportMatches
-RoundNo

So if the teams are directly related to the round, then this is the structure you would need

tblSportMatchRoundTeams
-pkSpMatRdTeamID primary key, autonumber
-fkSpMatRdID foreign key to tblSportMatchRound
-fkTeamID foreign key to tblTeams
-logHome (yes/no field to designate which team is the home team)
-a field to hold either the points (if you want to track the actual score & then you can determine the winner via a query) or a yes/no field to designate the winner

tblTeams
-pkTeamID primary key, autonumber
-txtTeam

I was not sure where to put location since I did not know if the location referred to the match or the round. I'll let you handle that.

[rpeare]

I'd like to add something else, I like JZWP's suggestion for making your database capable of tracking multiple sports, you do NOT need 2 team tables, but you will likely want to restrict your data entry to teams that are NOT chosen in TEAM1 or TEAM2

Let's say you have four teams
LIONS
EAGLES
FALCONS
BEARS

If you put LIONS in team1 you don't want lions to appear in team 2
let's now assume you have LIONS and EAGLES playing one another (team1, team2 respectively) if you realized you have put the lions in incorrectly you do not want EAGLES to display in the TEAM1 list if you go to modify it.

Secondly you are not storing a score, I don't know if that's important but you could determine the winner if you had a Team1Score Team2Score field as opposed to a winner field. If you maintain the winner field that will likely have to also link to the teams table.

[Juice118]

Wow thanks so much guys, clear concise answers that will give me plenty to start learning and work with.

In my case the round basically refers to a week in the season. For example say there were 10 teams in the competition and all of them play on the weekend - there would be 5 matches on that weekend these 5 matches would be 1 round. So a season of 20 weeks would have 20 rounds. I am including it because in the end result I would like to display a table with each person and their 'tipping' score for each 'round/week' as well as their season total.

The location is not important it is just the name of the venue for the match. The score of each match does not really matter as it is the number of correct winners tipped by people that I am really recording - But I am going to put it in there to add that extra dimension to me learning the relationships so thanks for the explanation rpeare. I will also try and add other sports once I get a grasp of it - good idea jzwp11 that will really get me thinking.

The end result I am aiming for is an output something simple like this:



I am still trying to get my head around how to go about getting to this and am assuming I just need to do a query once I have my database sorted out that counts the number of times each individual person's tipped winner is equal to each actual match winner for each round. (seems like thats going to be a lot of seperate queries to fill out the leaderboard table shown above? especially if done external to access such as using SQL to display in a browser) It would be a seperate query for every person for every match of every round.

Thanks again, really appreciate it.

[rpeare]

The result you wanted from the structure other people have mentioned is called a crosstab query, easily doable if you maintain a normalized data structure.

[rpeare]

Of course I have no idea how you win at tipping