PBeM to PBBG

[Sinzygy]

So I recently sat down and found my old pbem-stuff. For those who don't know what a pbem game is, it's a game that is played by sending an email to a certain emailadress and once every week all the sent emails are analysed and the orders are executed.

Now I want to create something simillar, but this time not with by email but with a PBBG. Basicly all I'm doing is writing the whole engine in PHP and provide an interface for the player to make his move.

So far I got all the data for the different buildings, etc. But I need help with how the whole engine works. The player has multiple villages which he controls and he has to transport resources betweeen those villages and issue commands on what to do in the village.
I was thinking about using a table for storing the commands for each village. So far this would be my tables:
command: ID, vill, target, command, citizens
c_create: commandID, createID, amount
c_transport: commandID, transport, amount

The commmand-table fields:
ID: the ID of this specific command
vill: the village from which this command was issued
target: the village that is the target of the command
command: the actual command. This can be: build, recruit, gather, transport, attack
citzens: this is the number of citizens used with this command. gather needs an amount, transport needs an amount, builds need an amount. for the other command, there is no need for a specification

if the command is build or recruit, then the c_create table will be used.
commandID: which command this is needed for
createID: either which building (if in command=build) or unit (if command=recruit) the player wants to recruit
amount: how many of these recruits will be issued

if the command is transport or attack, then the c_transport table will be used
commandID: which command this is needed for
transport: what to transport (this can be either a resource such as wood, iron or a unit such as citizens, swordman)
amount: how much of that resource will be transported

the user has to assign a certain amount of citizens when building, gathering or transporting something (yes, including unit-transport). The amount of progress done is dependend on those units and their morale. <- but that is the easy stuff

So, now a couple of questions:
[1] Does this seem reasonable?
[2] What should I change?
[3] Is mysql last_insert_id a reasonable way to link the command-tables?
[4] Is there an easier way?
[5] Any possible problems with this?

Thanks

[Zeggy]

Nice idea
1. Yeah, it seems reasonable to me. It could also be done with one table but I guess that's just what you prefer

2. Can't think of anything to change atm. I've never played such a game before, although it does sound similar to a tick-based game.

3. Yes.

4.  Sounds fine.

5. You won't know until you've tried it

[Sinzygy]

Here is some new stuff to consider:

As of right now, the villages are represented by tables with 23 fields. those fields include buildings and available resources in the village.
Should I split this table up into 3 tables? 1 For the village, 1 for the resources and 1 for the buildings? I don't know which one is better, but I feel more comfortable with just one big table. Also I would only need to query the DB once to get all (or most, since units are not included) data for a village.

I've got the same problems with my units. A unit is a group of units (yeah, stupid choice of words, I know but it can't be helped atm) and there are a couple different types of units. Right now I'm doing it the same way as with the village, that means 1 table and one field for each unit-type. Most of the time some of these fields will be left blank. Should I split this table up or not?

I need a sup-table for the villages and units a) for easier management and b) movement control and other factors like experience, morale, and need of food.