Help with database design

Question

Hey im new to database design and having trouble trying to figure this one out. I have two tables Team and Fixtures. Team has rows of football teams and Fixture has 2 of those football teams in each row (home and away team). I want to link team id to home_team and away_team but it doesnt allow me to. Please tell me how i can solve this.

Here is an image of my tables/relationships http://i49.tinypic.com/288qwpg.jpg

Answer 1

The fixture table could have this structure:

team_id INT,
team_type char(1) -- 'A' for Away, 'H' for home

thus, you have two instances of one team, differenced by a flag which tells if it is away or a home case.

this is one option, other people may have other design ideas, just wait and see what other people think

Answer 2

Here's what you could do:

Team Table

Team_ID
Team_Name

Fixture Table

Fixture_ID
Home_Team_ID
Away_Team_ID

The Home_Team_ID and Away_Team_ID links to the Team table.

---

Here's the SQL to tie them together (done in OpenOffice.org Base, but I hope it works for you as well):

SELECT Fixture_ID,
       Home_Team.Team_Name AS Home_Team_Name,
       Away_Team.Team_Name AS Away_Team_Name
  FROM Team Home_Team,
       Team Away_Team,
       Fixture
  WHERE Home_Team.Team_ID=Fixture.Home_Team_ID
    AND Away_Team.Team_ID=Fixture.Away_Team_ID

As you see, there are two references to the team table, but they're separated with aliases (Home_Team and Away_Team). I hope this helps.

Answer 3

Because you need to reference two different teams (home and away) from the fixture table, you need to add the team table twice. If you only add it once, then both home and away must relate to the same team, which of course makes no sense.

The team from one table is joined to the home team id. And the team from the second team table is joined to the away team id.

In access, you can also rename the table - give it an alias. (Click on the table an hit F4. This brings up the properties - you can type in an alias.) For the table linked to home team, call it (say) "homeTeam" and similarly for awayTeam.

You can then drag the name column from both these tables, to see side-by-side the names of teams playing in each fixture.

Answer 4

You're on the right track with MDMA's instructions. Try this:

1. Make a new query.
2. Add tblTeam
3. Add tblTeam again (It will automatically rename itself to tblTeam_1 or something similar)
4. Add tblFixture
5. Join tblFixture.HomeTeam to tblteam.id
6. Join tblFixture.AwayTeam to tblteam_1.id

Answer 5

For Access 2007, the easiest solution is to create aliases for your tables.

1. 'Show' the table (teams) twice in the relationships window
2. Right click each table and modify its properties
3. In the properties dialog, change the alias name (home_team, away_team) for the tables you have shown above.

4. Take the PK (team_id) from each alias (home_team, away_team) and relate it to your fixtures table:

   (home_team.team_id -> fixtures.home_team_id; away_team.team_id -> fixtures.away_team_id).