[web] Database Structure Ideas

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 =

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 =

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]

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

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.

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 :)

Thanks for the replies. I will most probably do some designs at a later date, it is ok as it is and the best thing, it works! :D thanks again!