mysql - Database design to store lottery information -

i designing system supposed store different types of lottery(results + tickets). focusing on mega millions , singapore pool toto. both have similar format.

mega millions: 5 different numbers 1 56 , 1 number 1 46. toto: 6 numbers 1 45

i need come elegant database design store user tickets , corresponding results. thought of 2 ways go it.

1. just store 6 six numbers in 6 columns. or
2. create table(many many) has ball-number , ticket_id

i need store ball-numbers results well.

for toto if numbers match 4 or more winning numbers, win prize. mega millions there similar process.

i'm looking pros , cons or possibly better solution? have done lot of research , paper work, still confused way go it.

since once day thing, think i'd store data in easy edit, maintain, visualize way. many-many approach work. mainly, i'd want easy find users chose particular ball_number.

users   id   name  drawings   id   type # mega millions or singapore (maybe subclass drawing)    drawing_on  wining_picks   drawing_id   ball_number  ticket   drawing_id   user_id   correct_count  picks     id   ticket_id   ball_number 

once numbers in, find user_ids pick particular number in drawing

get drawing date

drawing = drawing.find_by_drawing_on(drawing_date) 

get users ball_number , drawing.

picked_1 = user.picked(1,drawing) picked_2 = user.picked(2,drawing) picked_3 = user.picked(3,drawing) 

this scope on user

class user < activerecord::base    def self.picked(ball_number, drawing)     joins(:tickets => :picks).where(:picks => {:ball_number => ball_number}, :tickets => {:drawing_id => drawing.id})   end  end 

then quick array intersections user_ids got 3,4,5,6 picks correct. you'd loop through winning numbers permutations.

for example if winning numbers 3,8,21,24,27,44

some_3_correct_winner_ids = picked_3 & picked_8 & picked_21  # array intersection 

for each winner - update ticket correct count.

i may potentially store winners separately, index on correct_count, , not data in tickets, ok now.