3

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)

  • I think you really mean archive when you say backup as it seems that your backup operation is removing data from the sweepstakes_items, sweepstakes_entries, and sweepstakes tables. – Mark Iannucci Dec 26 '14 at 20:46
  • Yes that is what i meant. I am sorry for the confusion – vasil7112 Dec 26 '14 at 20:58
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.

  • Alright thank you for your opinion.. I believe that this is the best way too. Do you believe that there could be a more efficient way to do it? I think though that the System 2 is good in terms of speed/design e.t.c. Thank you for your contribution – vasil7112 Dec 26 '14 at 21:01
  • Off the top of my head, I'm not coming up with something that I think may be better. I upvoted the question in the hopes that it gets more attention and someone posts a better answer. – Mark Iannucci Dec 26 '14 at 21:05
  • I trully appreciate that Mark. Have a nice night. Merry Christmas. – vasil7112 Dec 26 '14 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.