I'm building a gambling-related website where people can be on soccer fixtures (matches). A fixture ends up with a result: home win - draw - away win. I have one table for teams and one table for fixtures and another for bets. The fixtures table has a home team and an away team. But how do I model a draw bet? It's easy to have the bet table refer to the fixture and refer to the team. But a draw?
The most basic system:
3 tables, one for teams, one for fixtures and one for bets. Outcomes are represented by:
0 => Draw, 1 => Home win, 2 => Away win
Teams:
id | name
-----------------
1 | Some team
2 | Some team
3 | Some team
Fixtures:
id | home team | away team | result
--------------------------------------
1 | 1 | 3 | 0
2 | 2 | 3 | 2
3 | 1 | 3 | 1
Bets:
id | fixture | outcome
---------------------------
1 | 1 | 0
2 | 2 | 1
3 | 1 | 3