架构- 2外键到相同的主键?

I'm having 2 tables, named Teams and Matches - I want to be able to add a Match which consist of 2 Teams, but also be able to get all the Matches for a specific Team.

我有两个表，命名为Team和Matches——我想添加一个由两个Team组成的Match，但也能获得一个特定Team的所有匹配。

What I can do is:

我能做的是:

• A) Make a Many-to-Many relationship between the Teams and Matches table

  A)在团队和匹配表之间建立多对多关系

• B) Make two extra columns in the Matches table named HomeTeam and AwayTeam which is foreign keys that refers to a Team in the Teams table.

  B)在比赛表中增加两列，分别是HomeTeam和AwayTeam，这是指队表中的一个队的外键。

We all agree on that B sounds best, since I know the exact amount of teams that will participate in a match everytime - right?

我们都同意B听起来是最好的，因为我知道每次参加比赛的确切人数——对吧?

Now when it comes to declare this relationship in my entities, then I'll need to have 2 Many-to-one relationships to the Match entity, since the Match entity have 2 foreign keys that refers to a Team - and sine the number of foreign keys / references must be the same from both Match and Team, then I'll end up with something like this:

现在谈到宣布这种关系在我的实体,然后我将需要匹配实体2多对一的关系,自匹配实体有两个外键,指的是一个团队,正弦/外键引用的数量必须相同匹配和团队,然后我会得到这样的:

// Team.cs

/ / Team.cs

public class Team
{
    public virtual int ID { get; private set; } 
    public virtual string TeamName { get; set; } 
    public virtual Cup Cup { get; set; } 
    public virtual IList<Match> HomeMatches { get; set; } 
    public virtual IList<Match> AwayMatches { get; set; } 
    public virtual IList<Match> Matches
    {
        get { return HomeMatches.Concat(AwayMatches).ToList(); }
    } 

    public Team()
    {
        HomeMatches = new List<Match>(); 
        AwayMatches = new List<Match>();
    }
}

public class TeamMap : ClassMap<Team>
{
    public TeamMap()
    {
        Id(x => x.ID); 
        Map(x => x.TeamName).Not.Nullable(); 
        References(x => x.Cup, "CupID"); 
        HasMany(x => x.HomeMatches).KeyColumn("HomeTeamID").Inverse().Cascade.AllDeleteOrphan(); 
        HasMany(x => x.AwayMatches).KeyColumn("AwayTeamID").Inverse().Cascade.AllDeleteOrphan(); 

        Table("Teams");
    }
}

// Match.cs

/ / Match.cs

public class Match
{
    public virtual int ID { get; private set; } 
    public virtual Team HomeTeam { get; set; } 
    public virtual Team AwayTeam { get; set; } 
    public virtual int WinnerID { get; set; } 
    public virtual Cup Cup { get; set; }
}

public class MatchMap : ClassMap<Match>
{
    public MatchMap()
    {
        Id(x => x.ID); 
        Map(x => x.WinnerID); 
        References(x => x.HomeTeam, "HomeTeamID"); 
        References(x => x.AwayTeam, "AwayTeamID"); 
        References(x => x.Cup, "CupID"); 

        Table("Matches");
    }
}

As the code shows, then I'll have to use .Concat() to merge the HomeMatches and AwayMatches for a team, to get all the matches for a specific team..

正如代码所示，然后我必须使用.Concat()来合并一个团队的家庭主妇和客场比赛，以获得一个特定团队的所有比赛。

Is this really the best way?

这真的是最好的方法吗?

3 个解决方案

#1

B is the best way to go because A is a bit of a red herring. You don't really want a many to many table between Matches and Teams but the reason you don't want them is not because you know the number of teams that will be in a match, but because a match is actually a many to many relationship already for team to team.

B是最好的方法，因为A有点转移注意力。你真的不希望很多很多表之间的匹配和团队,但你不希望他们的原因不是因为你知道团队的数量将在一场比赛,但是因为比赛实际上是一个多对多关系已经为团队的团队。

It just happens that in this case when you have a many to many relationship between two teams you call it a match and it has it's own set of properties (time, date, location...).

在这种情况下，当两个团队之间有很多关系时，你称之为match，它有自己的属性集(时间、日期、位置…)。

Match should definitely have two foreign keys to team as match is your many to many table.

Match应该有两个外键，因为Match是你的很多很多的表。

#2

In a relational model it would look something like this. So two foreign keys is fine, the HomeTeamID and AwayTeamID are so called role names.

在关系模型中，它看起来是这样的。所以两个外键很好，HomeTeamID和AwayTeamID被称为角色名。

#3

This may in fact be the best way to go. I think you are just having a problem with the fact that you need to join two lists together to pull what seems to be a simple query. However, the structure you are using isn't just relating the two teams, it is providing an almost-hierarchical structure, i.e. HomeTeam = Parent, AwayTeam = Child.

这实际上可能是最好的方法。我认为您只是遇到了一个问题，您需要将两个列表连接在一起，以提取看起来很简单的查询。然而，您所使用的结构并不仅仅是与两个团队相关，它提供了一个几乎是层次结构，即HomeTeam = Parent, AwayTeam = Child。

If you want to simply relate the two, you can create the Many-To-Many like you said:

如果你想简单地把两者联系起来，你就可以像你说的那样创造出多对多的人:

[Team]

[MatchTeam]
TeamID
MatchID
IsHomeTeam

[Match]