mysql relational schema help needed

I'm coding a little web-based game with php. It's going to essentially be a strategy-type game, with harvesting resources and building up a military as the primary focus.

Anyway, the program will allow the player to have access to more than one type of race. Players default with humans, but as they play they may be able to entice up to 5 out of 10 additional races to join their estate.

And here is where I run into the relational question - I think I know the answer, but I'm no relational schema deity so affirmation would be great.

The simple way to account for this in the database would be add fields for every race (total pop) and what they're assigned to do. (woodcutters, goldminers, etc.)

But then I realized a better way would probably be to make a separate table called raceinfo, which would hold populations and assignments. a row might look like this:

userid, raceid, totalpop, woodworkers, goldminers... and so on.

This way the script, when generating the overview for the player, would hit up the raceinfo table and find that userid 1 has 500 of "raceid 3", or dwarves, and they are dispositioned "thusly".

Is that clear enough?

the first way, just cramming it all into the playerinfo table, would be easy but cumbersome at the same time (big queries) but the second way I think is more realistically true to the relational ideal.

The second way is true to the relational ideal. Basically you want to have the least amount of redundant data in a table. Repeating "1" 1000 times takes up less space than repeating "Dwarves" 1000 times.

Your plan sounds good. I am interested to see your results.

What you would have is a table

races
id
race

units
id
user_id
race_id
assignement_id


stats
id
unit_id
type enum('Strength','Magika','Intelligence')
stat

assignments
id
assignment enum('Woodchoppa','Soldia','GoldMina');


This would allow you to use relational joins for all the information, say unit x has 3 strength and just increased one, you could merely

UPDATE stat SET stat=stat + 1 WHERE unit_id=x AND type='Strength';

Similiarly, selecting all of the units/races/stats would be simple.

SELECT race, assignment FROM units,races, assignements WHERE races.unit_id=unit.id AND assigements.unit_id=unit.id;

Then running that query would give you a nice litter array of all the units. You could get counts using a query like this

SELECT count(unit_id) FROM races,units where units.user_id=x AND units.id=races.unit_id

I love doing this stuff.,.. If you want more help, hit me up on AIM

yeah, i wondered about creating a distinct table for population assignments...

i think what i'm going to do, however, is just give the player a flat bonus for recruiting a new race to join their estate. This will result in a lot less micromanagement during gameplay. (and less server overhead as a result.)

I.E. - "The dwarves agree to migrate to your lands. As a result, you gain 20% to your mining rate and 10% to your craftsmanship rate."

Yep, that would work as well.

I would do a linking table:

race_user_link:
id
user_id
race_id

Then that way you can have multiple races per user.

Alternatively, simply adding a couple race_1_id and race_2_id fields would also work, if you wanted to limit them permanently to two races...

Yeah, I'm thinking permament.

What I'm considering now is having race1 - race5 fields in the main userdata table, as well as race1pop - race5pop. Then whatever units have been recruited can be totaled and subtracted from each respective race. doing it this way will give me an easy variable to modify when proliferation needs to be calculated. (mommy, where do dwarves come from? "a maintainence function, dear.")

Ah, Well, if you plan on having > 2, you should use a link table.