Best Design for this “Sweepstakes” Project

backupdatabase-designperformance

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:

users | id (INT[11] Auto Increment) | username (VARCHAR[30]) | real_name (VARCHAR[100])
sweepstakes | id (INT[11] Auto Increment) | creator_id (INT[11]) | title (VARCHAR[100])
sweepstakes_items …. DESIGN NOT IMPORTANT ….
sweepstakes_entries …. DESIGN NOT IMPORTANT ….
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)

Best Answer

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.

Related Solutions

Sql-server – Best way to design this mileage table

Computed columns are your friend. Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
Capture all data constraints using CHECK constraints.

Here is a pseudo-schema definition:

  id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

As for indexing the table, here's what I think:

Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme. You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.)
Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it.

When you're done, test your setup as follows:

Create a test table
Pump it full of test data
Create the indexes
Update statistics
Run your most common queries
Check their plans and run times

Mysql – Database redesign opportunity: What table design to use for this sensor data collection

You should think about partitioning the table for a big reason.

All indexes you have on a giant table, even just one index, can generated a lot of CPU load and disk I/O just to perform index maintenance when executing INSERTs, UPDATEs, and DELETEs.

I wrote an earlier post back on October 7, 2011 on why Table Partitioning would be a big help. Here is one excerpt from my past post:

Partitioning of data should serve to group data that are logically and cohesively in the same class. Performance of searching each partition need not be the main consideration as long as the data is correctly grouped. Once you have achieved the logical partitioning, then concentrate on search time. If you are just separating data by id only, it is possible that many rows of data may never be accessed for reads or writes. Now, that should be a major consideration: Locate all ids most frequently accessed and partition by that. All less frequently accessed ids should reside in one big archive table that is still accessible by index lookup for that 'once in a blue moon' query.

You can read my entire post later on this.

To cut right to the chase, you need to research and find out what data is rarely used in your 10GB table. That data should be placed in an archive table that is readily accessible should you need adhoc queries for a historical nature. Migrating that archival from the 10GB, followed by OPTIMIZE TABLE on the 10GB table, can result in a Working Set that is faster to run SELECTs, INSERTs, UPDATEs, and DELETEs. Even DDL would go faster on a 2GB Working Set than a 10GB table.

UPDATE 2012-02-24 16:19 EDT

Two points to consider

From your comment, it sounds like normalization is what you may need.
You may need to migrate out everything over 90 days old into an archive table but still access archive and working set at the same time. If your data is all MyISAM, I recommend using the MERGE storage engine. First, you create the MERGE table map once that unites a working set MyISAM table and an archive MyISAM table. You would keep data less than 91 days in one MyISAM table and rollover any data over 90 days old into the archive. You would query the MERGE table map only.

Here are two posts I made on how to use it:

Here is an additional post I made on tables with a lot of columns

Too many columns in MySQL

Best Answer

Related Solutions

Sql-server – Best way to design this mileage table

Mysql – Database redesign opportunity: What table design to use for this sensor data collection

UPDATE 2012-02-24 16:19 EDT

Related Question