How to store Goals (think RPG Quest) in SQL

Someone asked me today how they开发者_开发百科 should store quest goals in a SQL database. In this context, think of an RPG. Goals could include some of the following:

• Discover [Location]
• Kill n [MOB Type]
• Acquire n of [Object]
• Achieve a [Skill] in [Skillset]
• All the other things you get in RPGs

The best I could come up with is:

Quest 1-* QuestStep
QuestStep 1-* MobsToKill
QuestStep 1-* PlacesToFind
QuestStep 1-* ThingsToAcquire
QuestStep 1-* etc.

This seems a little clunky - Should they be storing a query of some description instead (or a formula or ???)

Any suggestions appreciated

• User can embark on many quests.
• One quest belongs to one user only (in this model).
• One quest has many goals, one goal belongs to one quest only.
• Each goal is one of possible goals.
• A possible goal is an allowed combination of an action and an object of the action.
• PossibleGoals table lists all allowed combinations of actions and objects.
• Goals are ordered by StepNo within a quest.
• Quantity defines how many objects should an action act upon, (kill 5 MOBs).
• Object is a super-type for all possible objects.
• Location, MOBType, and Skill are object sub-types, each with different properties (columns).

I would create something like this.

For the Quest table:

| ID | Title | FirstStep (Foreign key to GuestStep table) | etc.

The QuestStep table

| ID | Title | Goal (Foreign key to Goal table) | NextStep (ID of next QuestStep) | etc.

Ofcourse this is where the hard part start, how do we describe the goals? I'd say create one record for the goal in the Goal table and save each of the fields of the goal (I.E. how many mobs of what type to kill, what location to visit, etc.) in a GoalFields table, thus:

Goal table:

| ID | Type (type is one from an Enum of goal types) |

The GoalFields Table

| ID | Goal (Foreign key to goal) | Field | Value |

I understand that this can be a bit vague, so here is an example of what dat in the database could look like.

Quest table

| 0 | "Opening quest" | 0 | ...
| 1 | "Time for a Sword" | 2 | ...

QuestStep table

| 0 | "Go to the castle" | 0 | 1 | ...
| 1 | "Kill two fireflies" | 1 | NULL | ...
| 2 | "Get a sword" | 2 | NULL | ...

Goal table

| 0 | PlacesToFind |
| 1 | MobsToKill |
| 2 | ThingsToAcquire |

GoalFields table

| 0 | 0 | Place | "Castle" |
| 1 | 1 | Type | "firefly" |
| 2 | 1 | Amount | 2 |
| 3 | 2 | Type | "sword" |
| 4 | 2 | Amount | 1 |