6 Replies Latest reply on Oct 11, 2007 4:48 AM by CFMXPrGrmR

    Web-Based Game Skill System

      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
        • 1. Re: Web-Based Game Skill System
          Dan Bracuk Level 5
          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.
          1 person found this helpful
          • 2. Re: Web-Based Game Skill System
            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.
            • 3. Re: Web-Based Game Skill System
              *bump
              • 4. Re: Web-Based Game Skill System
                Level 7
                "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


                • 5. Re: Web-Based Game Skill System
                  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
                  • 6. Re: Web-Based Game Skill System
                    CFMXPrGrmR Level 2
                    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.
                    1 person found this helpful