MySQL design problem

[girasquid]

Hello, all.

Right now, I'm working on a project that involves Perl/CGI for interactions, and MySQL for data storage(it is a browsergame). There are certain skills that users can train in, that will affect their attributes.

In order to keep the game easy to extend, we need the skills not to be handcoded. They should all be stored within the database somehow, along with how they affect the player's stats(so that administrators can easily tweak to resolve balance issues).

So far, my idea is to have something like this:

skills
name | stat1boost | stat2boost | stat3boost | stat4boost


And then use the values to modify the user's stats.

However, this seems...kludgy. Can anyone point me in the right direction for a better way to do this?

Thanks,
Girasquid

[Fenris]

You should work with a many-to-many relationship, where you have a certain skill being connected to a certain "boost value" and "stat to boost". It will not necessarily be very easy to maintain, but here goes:

Each skill in the skill table needs a unique id (which it will most likely have).
Then, you need a table of stats-to-boost - basically, this is the list of stats, each with a unique id (which it too will most likely have).

Now comes the tricky part: connecting them. You need a third table: skill_stat_lookup. It needs two columns: skill_id and stat_id.

Into this table, you insert the relations between skills and stats.
For instance, if skill #2 will affect stats #3 and #5, then you insert two rows into the lookup table:
skill_id stat_id
2 3
2 5

Then, when you perform updates with a certain skill, you can grab the stats it affects with the following query:


SELECT * FROM stat_table, stat_skill_lookup WHERE skill_id=my_skill_id AND stat_id=stat_table.id


Then, you can do basically the same thing to build a lookup table between the skill and the actual modification values for each stat.

This is just a quick overview - please google for many-to-many relationships in relational database design. You'll figure it out in 30 mins. ;)