simple adventure game mechanics stored in database

Question

I plan to code a little adventure-like game. To be able to build multiple episodes without deploying new code I want to store any information possible in the database.

This includes stuff like

if player examines item [cell phone], the contact [xxx] is added into his phone book and the item [cell phone] is replaced by [old nokia phone with scratches]

So something like "SCUMM lite".

Of course, [cell phone], [xxx] and [old nokia phone with scratches] are objects that already exist in the database. But while [cell phone] is of type [inventory-item], [xxx] is of type [contact], so there can't just be no simple link between some database-tables

I got a very basic idea about how I could achieve this but wonder if anyone has done that before and can give me a hint or even best practice

Answer 1

The general solution you're looking for is commonly referred to as triggers. There are many ways to design these, but doing so in a database-friendly way is not hard.

The basic idea is that you do something in response to an event after testing it against a number of filters.

Filters can be as simple or complex as you want/need them to be. One possibility might be to make triggers be a simple attribute/constant comparision. For instance, HP<=100. You can store the attribute name, comparison operator, and constant in three columns in a SQL database.

Your triggers would then be another table that maps event type to trigger action, which will be one of a predefined set of enumerations (like kill, damage, teleport, etc.). You can optionally add a data column to supply to the trigger action like the amount of damage to do.

All of this can be extended to use equations or more flexible semantics in place of just constants, of course. You may need to do all the actual processing work outside of the database in this case, if that's ok for your game.

CREATE TABLE triggers(UNIQUE INT id, EVENT_TYPE event, ACTION_TYPE action);
CREATE TABLE filters(UNIQUE INT id, INT trigger_id, VARCHAR(16) attribute, COMPARISON_OP op, INT data);

-- kill the player if he steps on tile (10,20)
INSERT INTO triggers(@trigger = next_id(), 'MOVED', 'KILL');
INSERT INTO filters(next_id(), @trigger, 'position', '==', encode_vec2(10, 20));