Web-Based Game Skill System

[Guest]

Hello everyone,

I am currently developing a web-based game website using Coldfusion 8 and MS SQL.

Until recentely, things have been going well. But I am now stuck on a small issue of using an in-game skill system.

The way it works (or will work) is that your in-game character will have a large library of skills to choose from, all of which are accessible throughout the entire game. By choosing a skill, your character will begin learning it over a set amount of time (the time required for that specific skill). Your character will, over time, generate it's own library of learnt skills - each benefiting him or her within the game.

I hope this is all clear so far :)

So my small (but actually quite large) issue is that I'm not sure how to go about storing this list of learnt skills.
My first instinct was to create a column within the "characters" table in which the learnt skills would be stored as a list, and then drawn out when actually listed on a page. But I have heard that using lists within database tables isn't such a good idea, and I would generally prefer another way around the issue.
My next idea was to create a table listing all of the available skills as columns (a kind of library of every in-game skill), then the characters would have a single row in that table in which they have a "yes" or "no" under each skill column. These yes or no answers would then be used to generate the library of skills that character has. But this idea is very messy and I don't even know how I could use this table in an SQL query...

I'd really like a simple and powerful solution for this issue/problem, and I would be highly appreciative of anyone that could help me!

Thanks in advance,
Aiden

[Dan_Bracuk]

I would have "dimension" tables for skills, characters, games, and players. Skills, for example would resemble
Skill_id Skill
1 Singing
2 Dancing

Characters would resemble
Character_id Character
1 Bloggins
2 Smitty

Players would be the online constestants and Games would be used to identify a specific competition. Then you put it all together with some many to many relationships, all in one table. For the sake of a table name, let's call it GamePlayed. Fields would be
Game_id
Player_id
Character_id
Skill_id
other fields you need.

[Guest]

I see where your coming from, and your answer would have been a good solution in perhaps a sports game.

I should have stated how my game would be played or what the game actually is...
Rather than single game events, this web-based game would be an ongoing game, lasting for as long as the player plays (coming back day after day). So for a player to start playing one day and choose his or her skill to learn, that player would then be able to come back the next day (or within hours) and see the skill learnt, depending on the set time for that skill. This skill would then remain within the characters "skills" forever (or until the player is removed or the game entirely reset). The skill would, for example, unlock the ability to do another thing within the game - or even apply more damage to future "battles" when using a certain weapon type within the game.
I hope my general game type is becoming clearer now ;)

So rather than the skills, players and characters all being bundled into game events... the skills would have to be ever-lasting, and assigned only to the player/character than has learnt them.
These skills and their effects would then be applied whenever an action can be affected by the skill.

Hmm... I hope I'm making sense.

Thanks a lot for your reply anyway 🙂 and I'm sorry it wasn't of use to me.

[Guest]

*bump

[Newsgroup_User · Accepted solution]

"Thanks a lot for your reply anyway 🙂 and I'm sorry it wasn't of use
to me."

Actually Dan was guiding you down the correct database normalization
path. What you have is a classic many-to-many join. You have many
character records and many skill records and you want to join them such
that a character can have 0-N skills (zero to infinity) and a skill can
be used by 0-N players.

This is done with three tables. A character table with the basic
details of a character and a skills table with the details of the skills
and a joining table that connects the two. At a minimum the join tables
simple consists of two fields, a foreign key field to the characters
table and a foreign key field to the skills table. Thus for each skill
a character has there will be one record connecting the two id's
together. This joining table can easily be expanded to have other data
about the character-skill join such as when it started, what level the
character has reached in the skill, whatever else your system needs.

An simple Example:

Character Table
ID Name
1 Joe
2 Sam
3 George

Skill Table
ID Name
1 Hunting
2 Cooking
3 Archery

Character_Skill Join Table
CharID SkillID
1 1
1 3
2 2
3 1
3 2
3 3

Thus with basic SQL join syntax we can synthesize the data that Joe can
hunt and shoot arrows, Sam can cook and George can do it all.

HTH
Ian

[Guest]

Omg! I see now!

And as the crowd say when the magician reveals his trick... "and it was so obvious!" hehe :P

Well thanks for the help, both of you :)
I am very grateful.

Aiden

[Guest]

I'd be interested in seeing your game down the line when you have it ready. I've never seen a game programmed in ColdFusion and SQL, sounds cool.