Database Design for Betting Community

Question

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:

1. Everyone is welcome to register as a member; each member should have a name, email address and password.
2. 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").
3. 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.
4. 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.
5. 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.
6. 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!

Answer 1

Quite a task, but here is my attempt:

• Members table stores members data; including PerformanceFactor which is periodically re-calculated based on correctness of one's answers.
• Transactions table tracks money deposits and withdrawals for each member. The Type field could be 1=deposit, 2=withdrawal, 3=transfer from winnings.
• One contest can have many questions, a question belongs to one contest only.
• One member can place many member-bets, a member-bet belongs to one member only.Place UNIQUE constraint on MemberID, ContestID to prevent member placing several bets for a specific contest
• One member-bet has several answers, each answer belongs to one member bet only; each answer relates to a question. Place UNIQUE constraint on MemberBetID, QuestionID to prevent member placing several answers for a specific question.
• DerivedAnswers table contains answers obtained by "statistical analysis" of member answers for a specific contest. Place UNIQUE constraint on ContestID, QuestionID to prevent repeating an answer for a specific question.