Lottery number-tracking table structure (1 Viewer)

[mergle]

I am need of tracking previously drawn lottery numbers. Would it be best to have the drawn numbers be entered as separate fields in a given record (i.e., Ball1, Ball2...Ball5), or to create a one-to-many join table between a tblBall.BallID and a tblDrawings.DrawID?

I am vacillating back and forth, as I think I see advantages and disadvantages in both structures...so I am looking for more knowledgeable experience to set me on the right path.

 

[bob fitz]

IMHO it would be best to create a one-to-many join table between a tblDraws and a tblBallsDrawn. The PK of tblDraws would be the FK of tblBallDrawn.

 

[RossWindows]

I'm a little late to the party but I would definitely agree with bob here.
Since the order of the balls drawn during a draw is not important, there's no need to separate a set of balls into their respective columns.

Unless you wanted to store other information about each drawing you could even get away with a single table with two columns (drawDate datetime, ballNumber int).