Hi there,

I am only new to the forum and looking at gaining some knowledge in the database design world, I have created a few ER diagrams in the past but nothing too advanced.

A client of mine is looking at creating a lottery site to display past lottery results, along with division information, winnings etc and has asked for a proposal. My problem is that I cannot get my head around the table structure. It needs to work with multiple lotteries across the world, all with slightly different game structures and this seems rather complex. For example:

The total number of balls in the pool is game dependent. Some games have multiple pools
The number of balls picked is game dependent.
The division of prizes is game dependent.
Some games have supplementary numbers that come from the initial pool of balls.
Some games have a megaball/powerball that comes from a separate pool of balls.

I somehow need to gather all of this data into a single database structure.

Any ideas or assistance would be greatly appreciated, thanks.



Zac

Attached is what I have worked out to date, from what I can gather it "could" work but am unsure as to whether or not it is ideal.

There is also a problem with lotteries that include supplementary numbers in a single pool as I cannot figure out how to work out the divisions for these.

Any input would be appreciated.

Since you're only concerned about capturing historical results, I would take out the tables and columns describing game rules and other meta-information. That will simplify your model greatly.

I'd also suggest starting off by modeling types of games in which balls are only drawn from one pool. In this case, for each game, you'll have a "game event" (a date on which a drawing is done) during which a number of balls are drawn.

If you're willing to take a crack at this and post an updated model, I'll help you work towards a final model.