Result Based on Many to Many Relationship

Question

I wasn't exactly sure what to title this question, this is the problem I'm trying to solve.

I have two tables with a many to many relationship between them, Fight and Fighter. A fighter can be in many fights, and a Fight has many fighters (2 fighters). These are mapped fine but what I want to do is have a column in the Fight table in which I can set who won the fight between the two fighters. What would be the best approach to this?

Thanks in advance, if you don't understand I'll try to explain it in a better way.

public class Fight
{
    [Key]
    public int FightId { get; set; }

    [Key, ForeignKey("FightCard")]
    public int CardId { get; set; }

    public virtual ICollection<Fighter> Fighters { get; set; }

    }

public class Fighter
{
    [Key]
    public int FighterID { get; set; }

    [Required]
    [DisplayName("First Name")]
    public string FirstName { get; set; }

    [Required]
    [DisplayName("Last Name")]
    public string LastName { get; set; }

    [DisplayName("Nickname")]
    public string NickName { get; set; }

    [Required]
    public string Nationality { get; set; }

    public static String[] WeightClassOptions
    {
        get
        {
            return new String[] { "Heavyweight", "Light Heavyweight", "Middleweight", "Welterweight", "Lightweight", "Featherweight", "Bantamweight", "Flyweight" };
        }
    }

    [DisplayName("Weight Class")]
    public string WeightClass { get; set; }

    public string Height { get; set; }

    [Required]
    [DisplayName("Date of Birth")]
    [DataType(DataType.Date)]
    public DateTime DateOfBirth { get; set; }

    // Fight Record:
    public int Wins { get; set; }
    public int Loss { get; set; }
    public int Draw { get; set; }
    public int NoContest { get; set; }

    public String Picture { get; set; }

    public virtual ICollection<Fight> Fight { get; set; }

Rereading your question, a fight could have only a winner or do you expect to end also in a draw? — Steve, Jan 24 '13 at 23:45
Yes a draw would be possible also, I will update my question with the table schema. — Robert Pallin, Jan 24 '13 at 23:50

score 1 · Accepted Answer · answered Jan 24 '13 at 23:42

1

You could create a one-to-many relationship from Fight to Fighter called Winner.

answered Jan 24 '13 at 23:42

Ismael Ghalimi

3,515
2
22
25

Thanks for the reply, do you mean a winner table? – Robert Pallin Jan 24 '13 at 23:59
No, just an id column as @ppetrov suggested. The less tables you have to manage, the better. And to model a draw, I would just leave that value empty. Keep it simple. – Ismael Ghalimi Jan 25 '13 at 00:10
So you mean a normal int column and if the column value equals the fighter id then thats the winner, yes? – Robert Pallin Jan 25 '13 at 01:03
That's exactly what I would do. – Ismael Ghalimi Jan 25 '13 at 01:32

Russ Cam · Answer 2 · 2013-01-24T23:51:29.570

1

You should have a junction table that maps the Many to Many relationship between Fighters and Fights tables. Let's call this junction table FightersFights.

In FightersFights, you can add a column that indicates for each join whether the fight was won or lost. Something like this

| Fighters |         | FightersFights |             | Fights |
------------         ------------------             ----------
| Id       |         | FighterId      |             | Id     |
| Name     | 1     n | FightId        | n         1 | Name   |
|          |---------| Winner         |-------------|        |

edited Jan 24 '13 at 23:51

answered Jan 24 '13 at 23:43

Russ Cam

124,184
33
204
266

I am using entity framework with code first migrations, when I set up the relationship through EF and then use migrations this table is created automatically in the migration history. How would I handle this in the Entity Framework if I were to use this option? – Robert Pallin Jan 24 '13 at 23:56
1

aha, that changes things! The junction table is effectively hidden from you as an implementation detail in the impedance mismatch between the RDBMS and your object model :) The usual way that I have seen to solve this is to expose the junction table as an entity too, but it leaves for an awkward object model. Since you only have two fighters involved in a fight, you could have a `Winner` and a `Loser` reference on your `Fight` class that are both Foreign Key relationships to your `Fighter` table (and hence the properties on the Fight class are of Fighter type). – Russ Cam Jan 25 '13 at 00:11
Take a look at http://stackoverflow.com/questions/5418155/many-to-many-relationship-with-junction-table-in-entity-framework too – Russ Cam Jan 25 '13 at 00:13
Thanks for the reply, can you give me a quick sample how this would be done in code I'm not 100 percent on what you explained. – Robert Pallin Jan 25 '13 at 00:22

score 1 · Answer 3 · answered Jan 24 '13 at 23:45

1

Or you could add an idWinner to the Fight table that is a reference to the id of the fighter who won, this would avoid creating another table. You could also add a field winner which would contain 1 or 2 depending on which fighter won, but this one is messy

answered Jan 24 '13 at 23:45

ppetrov

3,077
2
15
27

cant' answer you in the comments of @ismael but that's actually what I meant (create an int column), and I think that's what ismael meant too – ppetrov Jan 25 '13 at 01:36

Result Based on Many to Many Relationship

3 Answers3