Database Schema

[butterscotchstallion]

spiffyrpg_players

Players are a subset of Units. user_id refers to the Limnoria user.

CREATE TABLE spiffyrpg_players(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          unit_type_id INTEGER,
                          character_name TEXT,
                          experience_gained INTEGER DEFAULT 0,
                          user_id INTEGER,
                          created_at TIMESTAMP);

spiffyrpg_units

NPCs!

CREATE TABLE spiffyrpg_units(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT,
                          type_id INT,
                          created_at TIMESTAMP);

spiffyrpg_unit_types

This is the "classes" table. By calling them unit types we can cover NPCs as well.

CREATE TABLE spiffyrpg_unit_types(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT,
                          created_at TIMESTAMP);

spiffyrpg_unit_type_titles

Unit titles which change with level

CREATE TABLE spiffyrpg_unit_type_titles(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          unit_type_id INT,
                          required_level INT,
                          title TEXT,
                          description TEXT,
                          created_at TIMESTAMP);

spiffyrpg_unit_dialogue

CREATE TABLE spiffyrpg_dialogue(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT,
                          context TEXT,
                          unit_id INT,
                          created_at TIMESTAMP);

spiffyrpg_effects

The interval_seconds column allows mechanics like damage or healing over time. stacks multiplies the effect. operator is '+' or '-'

CREATE TABLE spiffyrpg_effects(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT,
                          description TEXT,
                          operator VARCHAR(1),
                          hp_adjustment INT,
                          incoming_damage_adjustment INT,
                          outgoing_damage_adjustment INT,
                          interval_seconds INT,
                          stacks INT DEFAULT 1,
                          created_at TIMESTAMP);

spiffyrpg_unit_effects

Relationship between a unit and its persistent effects.

CREATE TABLE spiffyrpg_unit_effects(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          unit_id INT,
                          effect_id INT,
                          duration_in_seconds INT DEFAULT 0,
                          created_at TIMESTAMP);

spiffyrpg_items

An item can be anything, from a weapon to an amulet. equipment_slot determines whether the item can be equipped and in which slot.

CREATE TABLE spiffyrpg_items(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT,
                          description TEXT,
                          min_level INTEGER DEFAULT 1,
                          max_level INTEGER DEFAULT 100,
                          item_type TEXT,
                          rarity TEXT,
                          equipment_slot TEXT,
                          is_permanent VARCHAR(1),
                          unit_type_id INT,
                          created_at TIMESTAMP);

spiffyrpg_item_effects

Items can cause effects and like units, those effects can have a duration

CREATE TABLE spiffyrpg_item_effects(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          item_id INT,
                          effect_id INT,
                          effect_on_possession VARCHAR(1) DEFAULT 0,
                          duration_in_second INT DEFAULT 0,
                          created_at TIMESTAMP);

spiffyrpg_unit_items

Relationship between units and their items. An inventory.

CREATE TABLE spiffyrpg_unit_items(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          item_id INT,
                          unit_id INT,
                          created_at TIMESTAMP);

spiffyrpg_dungeons

Each channel is considered a "dungeon". Some dungeons may have a minimum or maximum level which determines the units inside, whether players can enter, and loot! Each dungeon has a theme which influences the units inside and what they say.

CREATE TABLE spiffyrpg_dungeons(
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          name TEXT,
                          description TEXT,
                          intro TEXT,
                          channel TEXT,
                          min_level INT DEFAULT 0,
                          max_level INT DEFAULT 0,
                          available_start_date TIMESTAMP,
                          available_end_date TIMESTAMP,
                          created_at TIMESTAMP);

[butterscotchstallion]

Moved to wiki