Multiple foreign keys to same primary key table

Question

I have a table with multiple fields that are foreign keys to a primary key in another table. For example :

Fixture Id (PK)
HomeTeamId (FK to Team.TeamId)
AwayTeamId (FK to Team.TeamId)
HomeTeamCoachId (FK to Coach.CoachId)
AwayTeamCoachId (FK to Coach.CoachId)

Would it be better to separate this data into 2 tables HomeTeam and AwayTeam with a foreign key to FixtureId? This is currently what was generated by Entity Framework :

FixtureId PK
HomeTeamId int
AwayTeamId int
HomeTeamCoachId int
AwayTeamCoachId int
AwayTeam_TeamId FK
HomeTeam_TeamId FK
AwayTeamCoach_CoachId FK
HomeTeamCoach_CoachId FK

This was generated through this class :

public partial class Fixture
{
    public int FixtureId { get; set; }

    //foreign key
    public int AwayTeamId { get; set; }
    //navigation properties
    public virtual Team AwayTeam { get; set; }

    //foreign key
    public int HomeTeamId { get; set; }
    //navigation properties
    public virtual Team HomeTeam { get; set; }

    //foreign key
    public int AwayCoachId { get; set; }
    //navigation properties
    public virtual Coach AwayCoach { get; set; }

    //foreign key
    public int HomeCoachId { get; set; }
    //navigation properties
    public virtual Coach HomeCoach { get; set; }
}

Can anybody tell me if this is the correct way to do this?

EDIT : In reply to Slauma

So my classes would basically look like this? Or does the configuration in OnModelCreating mean I don't need some of the foreign key related code in my Fixture class?

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Entity Type Configuration
        modelBuilder.Configurations.Add(new TeamConfiguration());
        modelBuilder.Configurations.Add(new CoachConfiguration());
        modelBuilder.Configurations.Add(new FixtureConfiguration());

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.AwayTeam)
            .WithMany()
            .HasForeignKey(f => f.AwayTeamId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.HomeTeam)
            .WithMany()
            .HasForeignKey(f => f.HomeTeamId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.AwayCoach)
            .WithMany()
            .HasForeignKey(f => f.AwayCoachId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.HomeCoach)
            .WithMany()
            .HasForeignKey(f => f.HomeCoachId)
            .WillCascadeOnDelete(false);
    }

public partial class Fixture
{
    public int FixtureId { get; set; }
    public string Season { get; set; }
    public byte Week { get; set; }

    //foreign key
    public int AwayTeamId { get; set; }
    //navigation properties
    public virtual Team AwayTeam { get; set; }

    //foreign key
    public int HomeTeamId { get; set; }
    //navigation properties
    public virtual Team HomeTeam { get; set; }

    //foreign key
    public int AwayCoachId { get; set; }
    //navigation properties
    public virtual Coach AwayCoach { get; set; }

    //foreign key
    public int HomeCoachId { get; set; }
    //navigation properties
    public virtual Coach HomeCoach { get; set; }

    public byte AwayTeamScore { get; set; }
    public byte HomeTeamScore { get; set; }
}

Answer 1

Apparently EF doesn't detect your int properties like AwayTeamId as the foreign key for the navigation properties like AwayTeam because the primary key property in Team is not Id but TeamId. It would probably detect the FKs if they are named like AwayTeamTeamId or if the primary key property in Team has the name Id.

If you don't want to change those property names according to EF convention you can define the FKs with data annotations:

[ForeignKey("AwayTeam")]
public int AwayTeamId { get; set; }
public virtual Team AwayTeam { get; set; }

// the same for the other three FKs

Or Fluent API:

modelBuilder.Entity<Fixture>()
    .HasRequired(f => f.AwayTeam)
    .WithMany()
    .HasForeignKey(f => f.AwayTeamId)
    .WillCascadeOnDelete(false);

// the same for the other three FKs

I have disabled cascading delete because it will be enabled by default for a required relationship. But because you have two required relationships to the Team table (and the Coach table as well) it would result in two cascading delete paths from Fixture to Team and Coach. Multiple cascading delete paths are forbidden in SQL Server, so you must disable cascading delete for at least one of the two relationships between Fixture and Team (and between Fixture and Coach).