Sign in to follow this  
binime

[web] Database Structure Ideas

Recommended Posts

Im currently making an web based RPG and I am incorporating NPC's into the game. They are stored in a table called "npcs". I want to be able to allow the user to interact (ask questions, get answers) with the NPC's, and I just cant seem to get my head around the way I would design this. I firstly thought about each NPC having set topics to talk about, i.e. making another field in the "npc" table called "topics" or something, but then I didnt know how I would relate this to the site through links and URI's etc. If anybody could help shed some light on this situation it would be greatly appreciated. Even some pointers would be great, or a way to do the NPC/User interaction. Thanks -binime (PS if this is in the wrong section, sorry! It seemed most relevant)

Share this post


Link to post
Share on other sites
Hi binime,

Here's a basic idea that should get you started. You could create another table for the questions/answers. Then create a relation table to link the questions/answers with your NPC's. When your players then talk to your NPC's, you just need to query the database for all the questions that the NPC can answer. ie:

NPCTable
npcid
npcname
npcdescription
...

QuestionTable
QuestionID
Question
Answer

NPCs_Questions (Relation Table)
NPCID
QuestionID

To have the character talk to the NPC, you just need to query the questions table to get the questions that the player can ask, ie
select QuestionID, Question
from QuestionTable qt inner join NPCs_Questions nq on qt.QuestionID = nq.QuestionID
where nq.NPCID = <the npc's ID - you should have this as a variable somewhere on the current page>

You can loop over all these questions and show them as hyperlinks that when clicked on will return the answer. (select answer from QuestionTable where QuestionID = <question ID that was clicked on>

Sorry ... gotta run (Im at work :) ... but you can make this much more complex if needed, like having some questions relying on others before they can be asked, storing the answers that characters already know for journals etc, having multiple questions return the same answer.... the list goes on. Just keep in mind that this is pretty basic, and more work would add some great functionality.


oh, just a clarification. The relationship table (NPs_Questions) allows multiple NPC's to have the same question. If you dont need or want this, you can put an NPCID in the questions table and get rid of the NPCs_Questions table.

hth,
Carpo :)

[Edited by - carpo on March 5, 2007 9:20:33 PM]

Share this post


Link to post
Share on other sites
Thanks for the reply carpo! I have it sort of working now, the basics anyway. I have made it lead on to other questions you can ask the NPC. How would I go about offering multiple answers based on changing factors? Would you recommend adding more answers to the question table, i.e.

question_id
question
answer1
answer2
answer3

Thanks once again! :D

Share this post


Link to post
Share on other sites
Think about the game structure first, and then once you have that decided, implement the database structure accordingly. If you have many-to-one (eg. many questions for one NPC) then you need 2 separate tables, with the 'many' table referring back to the relevant entry in the 'one' table. And if you have a fixed number of entities relating to one other entity (eg. never more than 3 possible responses to a question) then you can keep them all in the same table.

Share this post


Link to post
Share on other sites
Hi binime,

Im glad it helped. :)

As Kylotan mentioned, you should definately think through (and ideally document) how you need the different parts of your game to work. That will really help you come up with the best database design.

As for your question, it depends on how complex your multiple answer requirement is. If questions can only ever have 3 different answers, then i would keep it simple, and just add them to the questions table. However, if you need to get tricky and have many different answers for questions, including many different scenarios on when the answer is the correct one to give, or multiple questions with the same answer, then i'd probably create a seperate answers table, perhaps with information like an answer type, that determines when the answer is given.

Be aware though, this can get compicated quickly. Sometimes its better to keep it simple first ... you can always complexify it more later :)

Good luck!
Carpo.

ps. I always find it handy to initially develop my databases in something like MS Access first. Its really quick, and you can put mock information in it fast and write simple queries to see how it works. It also creates ok relationship diagrams that you can study and put in your documentation :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this