tracking population from a database perspective

[I like pie]

Ok, for starters let me say that the game I'm working on is a browser-based game. That is, the "population" is more a representation of a literal labor resource than a count of actual "people".

I've played a few games before that include population into the picture, but usually it's in a very limited fashion. What I'm trying to do is track population per player, and allow the player certain actions to perform based upon these numbers.

A bit of a breakdown for what I've got so far:

The player's world is divided into regions, of which are further divided into territories. Players start with a single territory of which they control and can acquire more. The most obvious resource from these territories is people, which will be the driving force behind everything the player ends up doing. Your army, your work force, your means of continued growth, will all be dependent on the number of people that live within a given territory.

I'm very excited to get this all working, but I can't get passed the initial problem: tracking the numbers.

I want to keep record of the population from each territory, and within that for each age. I see an obvious table that simply tracks just that: territory_id,age,count

But... I can't help but have the nagging feeling that I'm just going to bloat my db unnecessarily. At 5k territories, and 50 years of age (the people in this game will die at 50 on average), that's roughly 250,000 rows in the db that will be updated on a yearly basis in game-time, which may end up being as frequent as twice a day. I also have to keep scalability in mind; the 5k territories are confined within a 25x25 tiled grid. This map design is purely for testing and the eventual early betas. It will no doubt grow larger as the game (and hopefully user base) grows.

What I'm asking is this: what are your guys' take on what I have in mind? Is it overly-ambitious and/or impractical? I'd love to hear any critiques of this approach before I launch myself into code. This is going to be the backbone of the game world so I want to do it right.

[littleFella]

A few things to consider:

What is the required response time between be start of update transaction and availibilty of the updated result. If it's reaal time you may have slight issues here.

Is age defined with fine granularity or are we talking age groups? Age groups (if acceptable), would decrease the number of records signifficantly.

What database are you using? If it's one of those free MySQL versions then you are working with a pretty intensive processing. If you can switch to a database back end that supports stored procedures (Postgres, Firebird etc) then the time to update records will be cut down respectably.

The best way to see if the design you have in mind will be practical is to actually test it. Why don't you write a script (with whatever tools you are using) to:

1. create 250,000 records
2. run a batch update the records.

See how long #2 takes. Writing and running the test may be cheaper than a huge disappointment in case your design proves to be unrealistic.

Back to stored procedures: about a hundred years ago, when I still didn't quite grasp the benefit of a stored procedures, I run a similar test with Interbase DBMS. First run I ran a script to create 1,000,000 random records from the DB engine itself (using Delphi). I aborted after 50 minutes (about 40% of records were inserted). Then I re-ran the test but using stored procedures. This was successfully completed in 7 minutes. Based on that, and some other things I was able to learn along the way I would not even consider free MySQL versions for any semi-serious project.

[I like pie]

What is the required response time between be start of update transaction and availibilty of the updated result. If it's reaal time you may have slight issues here.

This shouldn't be too much of a problem. There will be designated "downtime" on the hour for the game to perform a series of necessary calculations based on user input from all the players. Even if it's as long as 5 minutes, the worst thing that can happen is that it will drive the players to the forum while they wait.

Is age defined with fine granularity or are we talking age groups? Age groups (if acceptable), would decrease the number of records signifficantly.

Somewhere in between. It's not detailed enough that "people" will be born on a given unit of game-time, but it's not really "age groups" either. I'm going to have to have it at least broken down by years. The game's population will effectively all share the same birthday. That is, population stats will be generated no more frequently than on a yearly basis in game time.

What database are you using? If it's one of those free MySQL versions then you are working with a pretty intensive processing. If you can switch to a database back end that supports stored procedures (Postgres, Firebird etc) then the time to update records will be cut down respectably.

Yep, MySQL. I might eventually look into something a bit more advanced, but I am also looking to potentially grow this into a collaborative effort, so I want to keep my code a bit easy to understand.

I'll take your advice on the testing and hope it's not too unrealistic. I'm not against changing my design. In fact, I'm expecting it. But I really like what I have on paper right now, so I'll be ecstatic if I can get it working without too many problems.

Thanks for the reply, it sounds like you've got a good grip on db related issues.

[I like pie]

Well shit, I hit another problem already haha. It takes roughly 5 minutes to insert 250k rows. Not horrible, but not good either. Then again, this would be a twice a-day occurrence and will exist independent of the regular hourly updates, so it will be a relatively infrequent process.

But that's inserting, and I inserted them all with a common number. This is not how it will work in the long run.

And that's where I hit my problem... I don't quite know how to go about manipulating this many rows. Each year, the age of all the pop shifts upward. It's an easy enough concept to grasp, but I can't figure out how to write it out.

A given territory would look like this assuming the max age is 3, and no children are ever born.

year 1:
territory_id,age,count
1,0,2
1,1,0
1,2,4
1,3,0

Year 2:
1,0,0
1,1,2
1,2,0
1,3,4

How would I represent that in code? My first thought is to store the entire pop table into an array, and use some sort of loop that starts at the top (3 in this case) and pulls the count from the age 1 year younger, and iterates backwards to 0 through the entire age range.

And then update the table in the db with the new values of the array. But it feels like I'm going about this entirely wrong.

Is there a better way to manipulate large amounts of related data? Or even, a mathematical way to represent shifting data across a range of rows?

[I like pie]

Oh man, I'm a freakin moron haha.

I don't have to do any shifting, I just have to update the age column. For some reason I was thinking that the age of a given row has to be unchanging, and I have to update a given row with the value of the age prior.

Talk about a too-complex solution to a simple problem. Bleh. I hate it when I think about something way too much and the solution is obvious and right there.

[Arenlor]

Lol, which column needs to be messed with lol, yeah that's often the issue I come across, what do I change and what do I leave. But do you need to have them in years? Like can't you have 0-5, 5-10, 10-15,...,45-50 that sort of thing? Also, I've played many tick and turn based games, this seems tick based, the question is, will a 0 year old count towards population that can work? I mean give 0-50 wouldn't you have 10-45 year olds doing work, the rest being too young or old, and what's up with dying in a tick based game? If there is a population work force you should have it be stable with them gaining or losing population each year. Even with turn based games they have you gained 10 population this turn. This game sounds interesting though, when you have it alpha ready let me know, I would love to play it.

[I like pie]

Well, initially I didn't want ages broken up at all. It was a much simpler design and not nearly as much work for the db to do. There was alot of cool/weird things that determined how the population evolved. For instance, if the work force is only adults, then I needed to determine when children become adults. Of course, if you don't want to track individual years you can't do this directly. So what I did was base it off of statistics. If 1 child becomes an adult at 20, then over the course of 20 years each child has a .05 chance of becoming and adult that year. It doesn't make sense in our world, but statistically it works pretty well. Given any number of children, over the course of 20 years roughly the same amount will have become adults.

The problem with this approach is, that while it works well enough for a scenario on its own (children becoming adults, adults dying, adults reproducing), it's absolutely terrible when these numbers effect other processes. For example, a child can potentially be born, become an adult, have a kid with another adult, and die all in the course of 4 in-game years. Because of the way the various age groups interact with each-other, it really skews any population statistics to the point that they're incredibly unrealistic, and far too varied from player to player.

After playing with this for awhile in hopes that I could solve that problem, I came to the realization that if I want to track growth in a meaningful way, and on a deeper level than just a few simple formulas, I have to keep track of ages.

The ages will fall into various groups that will allow the player certain actions. Like you mention, children won't be working. How quickly a player can accomplish certain things will depend on the number of "workable" citizens he has. The value of children then becomes nothing other than the fact that they will grow to be workers. And once workers hit a certain age, they will fall into the category of pop that can reproduce.

And as far as dying, it's necessary to maintain balance. People will be reproducing, so people will need to be dying. The trick for me (and eventually, the player) is going to be figuring out how often people should be born. The player's army will also be derived from the base pop, which means if they're actively warring other territories, they will need stimulate population growth as much as possible, but within reason given other economic factors.

That's what I like so much about what I've got on paper. I've played a few tick-based games as well and it's always "your population starts at x and grows at a rate of y" and has little to do with anything other than maybe making the player money. There's a huge element of gameplay left out there as far as managing a seemingly organic society.

Needless to say, I've got a ton of work ahead of me. Unfortunately, I work alone on this and it's hard to pin down serious collaborators. It's stuff like that which causes me to waste time on "huge" problems like the one I thought I had in this thread, when all it takes is somebody else to say "well shit, all you gotta do is this."

But hey, thank god for communities like this one. I'm not all without help.

[Arenlor]

If you ever want someone to help code for you I'm here. And as I said, you have an alpha tester ready to help out too, the idea of death very much intrigues me, it's what'll make your game popular I bet.

[littleFella]

Originally Posted by I like pie

I don't have to do any shifting, I just have to update the age column. For some reason I was thinking that the age of a given row has to be unchanging, and I have to update a given row with the value of the age prior.

Talk about a too-complex solution to a simple problem. Bleh. I hate it when I think about something way too much and the solution is obvious and right there.

I had a chuckle here reading your prior post You woudda possibly killed the system with array processing for 250K records and they you'd have to process each db record anyway.

Oh, if your MySQL uses InnoDB instead of myISAM then you have transaction processing. That means you can save records one by one (which occurs in memory) and then commit after all records. Depending on the db, this would speed the updates by 30 to 60% (ballpark figure)

oops. forgot to ask. How are you updating the age? What the SQL?

[I like pie]

Haha yeah, this isn't the first time I've looked at something in a completely backwards way. Fortunately I seem to have a good sense of when I'm doing something and it feels like it's wrong.

As far as the sql itself, I haven't written anything other than what I used to insert all the rows. Before I even start playing with the numbers in the db, I want to figure out everything that's going to be happening to them. That's why I'm glad I came here before starting anything. Saved me lots of frustration haha.

@Arenlor

Thanks, I appreciate that. I've got quite a ways to go still, but I'm feeling good about where it's going so far. Hopefully it won't be too long before I come back here asking how to optimize my processes as opposed to asking how to create them.

[Arenlor]

I would play with them quickly first. Run it by them a few times and see if it's working and how long it's working.

[I like pie]

Alright... well I've come across yet another problem with my situation (boy this is alot more complicated than I thought). This isn't really a coding problem though, it's pretty much just math-related. But no use starting a new thread. Anyway, it deals with rate of birth of a given territory's population.

For the sake of simplicity, let's say there is only 1 territory. What I'm shooting for is my original idea of eligible couples having a .3 chance of having a kid on any given year.

An "eligible couple" is simply the number of people between a set range divided by 2. In this case, the range is 20 and 30 years old. This means for 2 people that are born on the same year, they're likely to have 3 children over the course of 10 years. Seems easy enough. Random number between 1 and 10, if it is between 1 and 3 then they have a kid.

Problem is, this only works out if I'm talking about a single couple.

Let's say we have 20 people between 20 and 30. 10 couples. How do I go about representing the same thing?

If I try the same method, I get a 3/10 chance of "success". But what does that mean? All eligible couples had a kid? No, that can't be right. That means on 7 out of 10 years no children will be born.

So... maybe .3 of the eligible couples will have children every year? This makes a little more sense, but completely removes any randomness from the situation. That means every territory owned by anybody will have a population that has a growth pattern exactly like everybody else's.

I need to find some middle ground between those two. I should be able to take two territories, start them at the same number of people, and give them a set number of years and expect a different end population count for each, with at least some children being born most years.

The .3 should represent probability, not a set percentage of growth.

Any ideas?

[Arenlor]

how high will the population be getting? Would it be possible to throw more than one random number, like throw a xd3 where x is the number of couples, any 1 would have a child while 2 and 3 would not? The only way I can see it being random is to throw a variable for each couple. Which may explain why you don't see random growth rates in games. Or maybe only take so much of the population at a time say 1/(1d*x where x = number of couples? That would allow you to randomize the number of couples who were fertile that year, and then you toss the die again to see if they had children.

[I like pie]

Well, I talked with some math-smart people about it and they brought up a good point; that if I'm expecting large population numbers then the results would barely deviate from the expected 30% anyway.

So now that's out of the way (finally). Now it's just a matter of figure out how to stunt pop growth. That's gonna be interesting

[Arenlor]

How to stunt it? You could always reduce the age range of breedables. Or have early deaths, like kids and stuff dieing of diseases and that sort of thing.