Handling database updates, both timed and real-time events

[kittrellbj]

Ok, it's been a while since I've last posted, been busy with my paying job and just as busy with my hopefully-will-turn-into-paying hobby: PHP programming.

 

Anyhow, I have come to a problem.  I usually come on PHP freaks for brainstorming and ideas, so those who have read my previous posts will understand that (although they are very few).

 

So, I will start with a brief synopsis of the plan: I am building a space empire economical/military simulation game.  You can claim a planet, build up your base and fleet, take over more planets, and so forth until you get to another player and you can kaplowee his fleet/bases/feelings.

 

Anyhow, I have tried to think about a way of doing this that is effective and efficient (it must be efficient since it will be an MMO strategy game), and I cannot seem to come up with a plan I am confident in.  I am not as experienced as many of the people on this forum, so I will describe what I am trying to do and what options I have come up with, and my concerns for each.

 

What I am trying to do:  since it is a strategy game, you will be able to get resources and money per hour.  Fleets move in pseudo-real-time (they have a start date (when they leave the source) and an end date (for when they arrive at target)).  You can construct buildings on the bases (they have a start construction date and a completed date).  You can research technologies (again, begin date and completed date).

 

So, some things are based on hours (you get X resources and X money per hour), while other things are based on dates (it left at this date/time, so it will arrive at this date/time).

 

I need a solution for updating the database when players receive their resource/money income, as well as a solution for updating the database when fleets arrive at targets, buildings are completed, and technologies are completed.

 

A complication is that new buildings that are completed will add to resource/money income in some cases, so the construction updating would have to be exacting.

 

So, here are the options I can see.  I would like comments from people with experience on these, as well, as I personally do not see a flawless way of making sure everything is correct to the second in the game world.

 

Option 1:  CRON (or other time-based updating).  This could work for the monetary gain system (and may be preferable to make sure everyone gets paid the correct amount on the hour each hour), but I have feared things like this for a long time; they do not always work with 100% precision, and I have heard things like this create a drag on the server.

 

Option 2:  Page-load updating.  This could work for updating construction and fleet movement.  Basically, a header function exists on every game page that updates a small number of records every time the page loads, updating the oldest records in the database first that have a flag identifying it for updating until the flag is unset.  This would make it so every player in the game is unknowingly updating everything in the game as they play.  The obvious downside to this is that if there are no players online, the game doesn't get updated properly.  (Of course, it would only take 2 or 3 players to be online to keep it updating, but it is still a concern.)

 

Option 3:  In addition to Option 2, a redundancy to update what the player is looking at first, to make sure they are at least updating their own bases constructions and fleet movements (to keep it current for the player viewing what they are viewing).

 

Option 4:  A combination of all options, where money/income/resources are accounted on the hour, and everything else is accounted while the players navigate around to different pages.

 

And, if any of the above options (or all options combined) would be the best bet, just how many records can be safely updated at a time in MySQL for it to be safe, and would it be necessary to lock the records that are being updated by one player so that another player doesn't start updating the same records?

 

Perhaps I would need resources and income to be updated by one source, such as CRON, (to keep the records secure) and let the others be updated by player interaction?  I am already starting to get a little confused again thinking about it.

 

Any help would be appreciated on this.  I must go to bed now (have to work in 5.5 hours), so I will answer in approximately 14 hours.  Thanks in advance for any help on this matter!

[Q695]

Cron Job?

[Q]

Hi, this sounds very interesting.

 

My idea is a little different than any of yours:

 

I would do it by letting all the users update for them selves.

 

Since you have end dates on everything you could make a script in the log in procedure to get everything up to date simple by math

 

Hope this helps, email me if you want any help actually coding this

[kittrellbj]

Yes, but handling it all at login might not help the things that need updating during the player's time online.

 

Perhaps you could elaborate on it some if I am missing something

[yanjchan]

I apologize for the dead topic bump, but this topic is almost *exactly* similar to what I am trying to do.

 

Would you guys recommend running a cron job every 15 seconds or so? (Actually, 4 different cron jobs executing at four different times in a minute)

 

I've heard that updating on page load might not be very scalable.

 

Sorry if this is in the wrong place.

[premiso]

Create your own topic about it. No need to bump dead topics when you can create your own.

 

As such I won't reply here. (other then informing you of such).