Best Design for this "Sweepstakes" Project?

Question

Hello there fellow developers,

Merry Christmas first aff all, and i hope you will have a happy new year soon. :)

I was having more or less a problem with an idea that came to my mind. I am currently making a Project. We are going to call this project for this threads sake "Sweepstakes Project". So in this "Sweepstakes Project" we got the following database tables:

1. users | id (INT[11] Auto Increment) | username (VARCHAR[30]) | real_name (VARCHAR[100])

2. sweepstakes | id (INT[11] Auto Increment) | creator_id (INT[11]) | title (VARCHAR[100])

3. sweepstakes_items .... DESIGN NOT IMPORTANT ....

4. sweepstakes_entries .... DESIGN NOT IMPORTANT ....

5. user_stats | user_id (INT[11]) UNIQUE | sweepstakes_entered (MEDIUM_INT[11]) | sweepstakes_won(SMALL_INT[11]) | sweepstakes_created (SMALL_INT[11])

The table NO. 5 is imaginary. It doesn't yet exist, but is the part of my question.

So here is my question: Which of the following 2 systems would you prefer, or do you have a third and better one?

We want when a user reach some specific statistics to give him a badge [Badge table already exists and is fully functional, not needed to include it here].

System 1. Each time a user creates/wins/enters a sweepstake to count all his previous sweepstakes created/won/entered and if he reaches the amount required to give a badge, then award him the badge.

System 2. Each time a user creates/wins/enters a sweepstake to add (++) that created/won/entered sweepstake in the user_stats table. Then check if the user has reached the amount required to give him the badge, and then reward him the badge.

System 1 PROS: * 1 Less query than System 2, * Less tables

System 1 CONS: * Can't keep backups since it will could break the badge system(When the database is backed up, the sweepstakes count will be wrong since it counts only the rows that are in the current table, not the ones that are also backed-up).

System 2 PROS: * Can keep backups -> Can make the DB faster by removing all old unwanted sweepstakes stats.

System 2 CONS: * 1 Extra query, * 1 Extra table, * Will require some extra time for each status update.

What system would you prefer? Do you have an alternative more effiecient way?

Kind regards, Vasilis Dimitriadis

(Please keep in mind that the database design may not be the best that exists and i would enjoyed if i recieved some feedback on how to make it better too)

Answer 1

Assuming my comment about Archiving vs. Backup is accurate, go with System 2.

You don't mention it in your post, but System 1 would require some sort of count operation on each insert into the sweepstakes_entries and sweepstakes tables. Initially the performance associated with the count will not be a problem. However, as your tables grow the BadgeEarned? query will get slower and slower.

What's worse, your best approach to reduce that burden will be to archive data about closed sweepstakes into a less active area in your database, but doing that will be difficult which you point out in System 1 cons.

So best to store the user_stats somehow and update them as necessary.