[SOLVED] Opinions wanted on which way to code a program

[kjtocool]

For the past few weeks I have been building a web application / text based game coded in php.  The game involves movies, and predicting how much money the top 10 movies will make week to week at the box office.

 

I was doing a bit of work on the game tonight, and ran into a fundamental issue that I became a bit worried about, and I wanted to get your opinion.

 

The basic deal is this:

 

There will be a section where users can view results of a specific week, a month, a year, all time, etc.  Each time someone queries any type of chart ... like the past week's results ... a month ago's results ... essentially any type of chart showing and ranking scores ... I was planning on querying all the relevant predictions, calculating their score, and then displaying the results ... each and every time someone queried some result.

 

 

My question is, do you think this is OK, or do you think it will create too large a server load?  The site has it's own dedicated server.

 

Each time I display a result, I would have to:

- Run a query to select all predictions

- Create a result set

- Iterate through the results

- Pass the 20 actual values and the 20 predicted values to a function which returns the users score

- Run another query pulling the user's name from a separate database based on the user_id

- Create an array with all username's and the corresponding score

- Sort the array

- Print out the results

 

I'm not so worried about the load for Weekly Chart's, but when it comes to things like a yearly chart, where I'd have to calculate 52 predictions for every user ... well the load could get nasty.  Same goes for the overall chart ... where I'd have to find the average for every user's prediction.

 

I suppose the alternate is adding a new column to the table where I store each user's prediction, and then running a script to update each prediction with a score, each week.  That was what I wanted to avoid, but the more I thought about it, the more worried I became about the load it would create on the server.

 

So I thought I would ask what you all thought ...

[awpti]

Depends on how hefty your site gets (datawise).

 

In general, I follow this:

 

If something -needs- to be calculated before displaying it to a user, precalc it every, say.. 10-15 minutes and store this in a seperate table. Use this table for the live display.

 

This helps you avoid any possible situation where the server will be crushed by requests and, hence, processing of code/calculations.

 

Sure, it's a bit of a time delay, but 15 minutes is pretty accurate. You can always just monitor the server and adjust code as-needed. If you design the app well enough, modifying/slightly refactoring code should be simple at worst.

 

If this is your chance to skirt this issue before it becomes one - do it. Just run it as a cronjob in the background.

[kjtocool]

I would say I can expect somewhere around 200-600 predictions each week.  And probably around 600-1500 participating users overall.

 

I like your idea of a table for live display, but in general, once the values are set, there set for good.  They only need to be updated once, and then there fixed (with the exception of a users overall average, which needs updated each week.  So I would need hundreds of results tables, which I'd have to update all the time.

[awpti]

Hmm..

 

I'd have to see a bit more of the idea and general table layout behind it. Maybe I assumed a bit to much (I assumed there were calculations on every page load). If this isn't the case, then it doesn't seem you have anything to concern yourself with.

 

If it's mostly static data, you could always cache it out into static HTML, thereby negating usage of the database from the picture entirely except for additions/updates. Update the cache as needed.

[kjtocool]

Database structure is like so:

 

Weeks

- week_id

- prediction_end

- weekend_start_date

- weekend_end_date

 

Predictions

- week_id

- movie_1_name

- movie_1_prediction

.

.

.

- movie_10_name

- movie_10_prediction

 

Actuals

- week_id

- movie_1_name

- movie_1_gross

.

.

.

- movie_10_name

- movie_10_gross

 

 

So each week, each user predicts what the top 10 movies will make.  I store their guesses in a database.  Later on, when the numbers come out, I store the real values in a separate table.

 

I want to show results ... so ... show which users have predicted the best over the course of a month, year, each specific week, etc.

 

As I see it I can either:

 

A) Update the Prediction table and storing the score the user gets.  To do this I would have to run a script every week.  Then, instead of having to select all the data, and running calculations on it, I could just pull the score, or in the case of a yearly chart, pull all the scores from a user and average them.  Much simpler than pulling all 10 names and the prediction for each, and running it through a function to get the score, and doing it for every user.

 

or

 

B) I don't store the score, don't have to run an update script every week, and just pull all the data every time anyone wants to see it.

 

I really can't decide.

[awpti]

In this case, I'd write a script that would simply output static content once every week. Have that one script run through, create new/updated content as required and modify position based upon accuracy.

 

This, again, negates needing to go back to the DB to pull content on a page refresh. This just makes apache/IIS send a flat file. Much easier on the server.

 

This would be another one of those 'run a script every week' things. Project like this doesn't need live DB interaction on the display side, so try to avoid it if possible.