MySQL design question

[python72]

I am working on lottery numbers where people can check their winning numbers, etc. I would like to include members area where each user's numbers can be stored and when the user logs in the winning numbers would be highlited, etc. I wonder what would be the best way to accomplish this keeping in mind that different users can play different number of lotteries. Right now I am tracking about 30 lotteries but hopefully this number will grow with time. Each registered user will have unique ID# based on auto increment value assigned at time of registration by MySQL at time of registration.

I was thinking to create separate table for each lottery where users would store the numbers thay play along with their ID. When user page is displayed the php would search through each table to see if given user plays given lottery and display the numbers or continue on to next lottery if this user ID does not show up in current lottery table.

I wonder if this design would work fine and what suggestions anyone might have to improve it or if it should be done in totally different way to avoid issues.

[requinix]

I suppose all these lotteries are completely different? Different sets of rules? Different numbers of... numbers?

 

There's an easy non-searchable way to store all the information: serialization. Besides the user and winning numbers tables there's a user+number table which links up 1) the user id, 2) their numbers, and 3) which lottery the numbers are for. You probably wouldn't need another for the lotteries - a short and simple hardcoded text value would be enough. "Their numbers" would be, for example, an array.

array(4, 8, 15, 16, 23, 42)

With a simple implode() you'd get

4,8,15,16,23,42

and that's easily reversable.

 

 

So here's the rationale:

1. Why make it non-searchable? The only kind of worthwhile search I can think of, for winning lottery numbers, would be what they were according to date. The date would be easy to search on, leaving the numbers themselves as raw data. I don't know why you would want to (for example) search on the winning numbers themselves, but then again I think the whole gambling thing is stupid so maybe I'm just don't have the right mindset to understand. By making it non-searchable you can gain a lot of performance and code simplicity.

2. Why no lottery table? Well, what would you put in it? Lottery name... I don't know what else. The way I see it, a lot of this stuff will be hard-coded anyways (how to display the results, etc) so creating a table to hold only some of the information isn't necessary. If you have an intelligent system that uses templates or something, the argument doesn't hold so go right ahead and use a lottery table.

3. Wouldn't a lottery ID be better than a string? How? An identifier is an identifier - the only argument is about data size, and since an int is probably 4 bytes you could just as soon put those 4 bytes towards actual, human-understandable characters. Even if you're not looking at the data manually, you're reading the code, and that's the same code that has the lottery specifics hardcoded. Magic strings are better than magic numbers. But this argument is subject to #2, and if #2 doesn't hold then this is disqualified anyways.

 

4. Why not separate tables? You'd be creating a bunch of tables and that would make your JOINs fiendishly difficult. In general, when designing a database, if you come up with a solution that involves changing the schema over time due to normal usage, it's a bad idea. Often the answer is database normalization, but when the format of the data is unknown that can be difficult. Trust me: I'm in the same situation where I need to design a database to hold unknown, unpredictable data, except I have the added gotcha that the data has to be searchable. (And I don't have a good solution yet.)

[Philip]

I'll make a quick point against non-searchable:

1. Why make it non-searchable? The only kind of worthwhile search I can think of, for winning lottery numbers, would be what they were according to date. The date would be easy to search on, leaving the numbers themselves as raw data. I don't know why you would want to (for example) search on the winning numbers themselves, but then again I think the whole gambling thing is stupid so maybe I'm just don't have the right mindset to understand. By making it non-searchable you can gain a lot of performance and code simplicity.

If separated out you could see more stats for each player. Example: % of time you pick winning numbers, % of numbers correct across for all players, etc. Even if not wanted now, it could be possible for the future.

[requinix]

I'll make a quick point against non-searchable:

If separated out you could see more stats for each player. Example: % of time you pick winning numbers, % of numbers correct across for all players, etc. Even if not wanted now, it could be possible for the future.

Okay, I can accept that. But I'll retaliate with "So store the %-correct figure alongside the serialized set of chosen numbers".

Even assuming a fixed set of numbers for all lotteries, a cursory thought* about what it would take to get those percentages only gives me horrible-looking queries, like

# average %-correct for lottery drawing $X ignoring the current user $Y
SELECT AVG((
IF(a.n1=b.n1,1,0)+
IF(a.n2=b.n2,1,0)+
IF(a.n3=b.n3,1,0)+
IF(a.n4=b.n4,1,0)+
IF(a.n5=b.n5,1,0)+
IF(a.n6=b.n6,1,0)
)/6)
FROM user_choices a
JOIN lottery_drawings b ON a.lottery_drawing_id = b.id
WHERE a.lottery_drawing_id = $X AND a.user_id != $Y

 

* Keep in mind I've been awake for the last ~18 hours.