database structure

Question

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?

Answer 1

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