I'm creating a online community for a soccer betting game available in my country. I've a pretty good idea how the whole system should work but I'm having some trouble figuring out the ideal database design and I need some help with it.
The usual work flow should be something like this:
- Everyone is welcome to register as a member; each member should have a name, email address and password.
- Each week a new betting contest is opened, each contest has a fixed set of "questions" (in this case each "question" is basically in the form of "Home Team - Visiting Team").
- Each member is free to cast his prognostic in the form of "1 X 2" (1: Home Wins, X: Draw, 2: Visiting Wins; for each "question") on all the open contests available together with an amount of money (see point 5). Only one prognostic per contest is allowed.
- On the end of each week all the contests are closed and a real bet is placed based on all the individual bets and the performance of each member (see also this related question). The placed bet should be publicly available for everyone to see.
- When the result of all matches is known it should be possible to "attach" (sorry, I'm missing the word) the amount of money of the prize (if the community gets lucky, of course). The prize should then be proportionally divided by the amount each team member placed on the bet.
- Each member can at any given time deposit or withdraw a variable amount of money to / from his account, there should also be a transactions page where all the deposits, prizes and withdraws are presented.
Bonus Question: Since I'm still pretty much green at "SEO friendly" URLs I would also be interested in learning how would you name all the segments involved in this system
I would very much appreciate any help in the design of a DB schema that can accommodate this whole scenario.
PS: I'll open up a bounty for this question, I'm currently having some issues with my Internet connection so I might take some time to read / comment on your answers.
Thanks in advance!