mysql – Database design to store lottery information

mysql – Database design to store lottery information

Two tables

tickets
    ball_number
    ticket_id

player
    player_id
    ticket_id

// optional
results
    ball_number
    lottery_id

With two tables you could use a query like:

select ticket_id, count(ball_number) hits
from tickets 
where ball_number in (wn1, wn2, ...) // wn - winning number
group by ticket_id
having hits = x

Of course you could take winning numbers from lottery results table (or store them in the balls_table under special ticket numbers).

Also preparing statistics would be easier. With

select count(ticket_id)
from tickets 
group by ball_number

you could easily see which numbers are mostly picked.

You might also use some field like lottery number to be able to narrow down the queries as most of them would concern just one lottery.

One table

Using one table with a column for each number might make the queries much more complex. Especially that, as I believe, the numbers are sorted, and there are be prizes for hitting all but one (or two) numbers. Than you might have to compare 1, 2, 3, ... with 2, 3, 4, ... which is not as short as straightforward as the queries above.

One column

Storing all entries in a string in just one column violates all normalization practices, forces you to split the column for most of the queries and takes away all optimization carried out by the database. Also storing numbers requires less disk space than storing text.

Since this is a once a day thing, I think Id store the data in an easy to edit, maintain, visualize way. Your many-many approach would work. Mainly, Id want it easy to find users that chose a 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 you get the numbers in, find all user_ids that pick a particular number in a drawing

Get the drawing by date

drawing = Drawing.find_by_drawing_on(drawing_date)

Get the users by ball_number and drawing.

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

This is a 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 do quick array intersections to get the user_ids that got 3,4,5,6 picks correct. Youd loop through the winning numbers to get the permutations.

For example if the winning numbers were 3,8,21,24,27,44

some_3_correct_winner_ids = picked_3 & picked_8 & picked_21  # Array intersection

For each winner – update the ticket with correct count.

I may potentially store winners separately, but with an index on correct_count, and not too much data in tickets, this would probably be ok for now.

mysql – Database design to store lottery information

I would just concatenate them using a convention and store them in one column.

Something like 10~20~30~40~50~!60
~ separates numbers
! indicates special number ( powerball, etc)

Have a sql table valued function split the result if you really need to have it in columns.