How to model a X vs Y vs Z relationship in a database?

Question

I'm thinking of making a 'fight' game where you can schedule events where fights between multiple people will occur.

IE: One PPV has many segments, each segment could be a 'promo' or a 'fight' - if its a fight you can have:

ie: 1 vs 1 (single) 2 vs 2 (tag) X vs Y (where X and Y can be any number from 1-5)

In the above examples you can do a relationship (1 to Many) or Many to Many.

ie: [Segment] has many fighters (left hand of the equation) [Segment] has many fighters (right hand of the equation)

This seems fine.

However, I get stuck when it comes to more complicated relationships.

For example if the match is now a triple threat like 1-vs-1-vs-1 or a four-way or six-way match (1 vs 1 vs 1 vs 1 vs 1 vs 1) I have no idea how to model this in a database that would make sense.

The original reason for using relationships was because the types of matches could be different kinds and modular.

The only way I can think of getting out of this is to have arrays or lists of people and make it a hard limit for each type of match.

But I'm wondering how other games of this genre build such relationships for their database.

Thus my query is -- how do you model a X vs Y vs Z relationship in a database?

Thanks now.

Answer 1

It sounds to me like you should be thinking in terms of sets for teams and matches:

• teams are sets of players (with 1 to 2 players based on your description)
• a match is a set of teams where each team fights the others in the set.

Since matches are sets and teams are sets, you could represent things like this:

{{P1}, {P2}} => P1 vs P2

{{P1, P2}, {P3, P4}} => P1+P2 vs P3+P4

{{P1}, {P2}, {P3}} => P1 vs P2 vs P3

This is all modeled with 1 to N relationships in a database.

Here is a database example for P1+P2 vs P3 vs P4 (ie. {{P1, P2}, {P3}, {P4}})

**Player**
+--------+
|PlayerID|
+--------+
|P1      |
|P2      |
|P3      |
|P4      |
+--------+

**Team**
+--------+
|TeamID  |
+--------+
|Team1   |
|Team2   |
|Team3   |
+--------+

**Team Players**
+------+--------+
|TeamID|PlayerID|
+------+--------+
|Team1 |P1      |
|Team1 |P2      |
|Team2 |P3      |
|Team3 |P4      |
+------+--------+

**Match**
+--------+
|MatchID |
+--------+
|Match1  |
+--------+

**Match Teams**
+-------+--------+
|MatchID|TeamID  |
+-------+--------+
|Match1 |Team1   |
|Match1 |Team2   |
|Match1 |Team3   |
+-------+--------+