php - Cheapest way of managing relational SQL data -

i'm trying find best method managing huge-relational game data.

let me explain data structure.

there 3 main data field. user, bets , coupons.

+----------------------------------------------------+ |                     bets                           | +----------------------------------------------------+ | id | status    | yes             | no              | +----+-----------+-----------------+-----------------+ | 1  | 0         | 1.45            | 2.52            | +----+-----------+-----------------+-----------------+ | 2  | 1         | 3.00            | 1.08            | +----+-----------+-----------------+-----------------+                     | 3  | 2         | 2.43            | 1.42            | +----+-----------+-----------------+-----------------+  +----------------------------------------------------+ |                     coupons                        | +----------------------------------------------------+ | id | played_by | bets            | status          | +----+-----------+-----------------+-----------------+ | 1  | 1         |1,yes;2,no;3,yes;| 0               | +----+-----------+-----------------+-----------------+ | 2  | 2         |2,yes;3,no;1,no; | 0               | +----+-----------+-----------------+-----------------+                     | 3  | 3         |1,yes;2,no;      | 0               | +----+-----------+-----------------+-----------------+ 

information: every bet has yes/no choice. users play bets. register them inside of coupons. if bets inside coupon win, coupon wins , user balance. classic. please note there many bets (avg. 5 per coupon), many coupons played users (thousands), , thousands of users.

so i'm trying find best method finalizing bets , checking coupons win or lose process.

method 1 tried;

• we finalized bet id: 2 yes;
• check 2,yes; "like" operator in coupon, if there is, concat(append) 1 progress field.
• check how many bets there inside coupon.
• if count of 1s equals numbers of bet inside coupon, set coupon status won.

method 2 tried;

• finalize bets; yes or no

• check related coupons cron task.

i liked both methods, want users see progress immediately, not sure cron method. both methods work fine, have doubts happen when there thousands of users.

i hope described issue understandable. i'm looking comments , suggestions.

thanks.

instead of appending user's bet value in coupon (which highly inefficient since you're having use like operator), makes more sense create table of coupons store id of bet associated it, id of user belongs to, , value of coupon (yes or no). coupon table following:

coupons  id  betid   userid  value 1   1       10      yes 2   1       11      no 

now if want acquire of coupons associated bet #1, select * coupons betid=1.

if bet #1 wins, need acquire value of bet winning choice, , update of users fall under choice. example:

# select winning value:  select <winning value> bets id = <id of completed bet>;  # update users:  update users set balance = balance + <winning value> id exists (select userid coupons betid = <id of completed bet> , value='<winning value>');