Database Design of loot tables

Question

Apologies for the length, I'm currently struggling to model a recursive loot table system in a PostgreSQL database for my game. Meaning, I want to have a main loot table, and then add sub-loot tables as needed. These could be themed tables or just tables of like items. I'd like to be able to add sub-tables several levels down the tree. At the moment, I have a working system that does not use a recursive approach, but would like to update it to allow for sub-tables.

This is the SQL for my Loot table:

CREATE TABLE LootTable (
    TableID SERIAL PRIMARY KEY,
    TableName VARCHAR(50) NOT NULL UNIQUE,
    AmountOfDrops INTEGER DEFAULT 1
);

This is the table for loot table contents:

CREATE TABLE LootTableObject (
    ItemID INTEGER REFERENCES Items(ItemID) ON DELETE CASCADE ON UPDATE CASCADE,
    TableID INTEGER REFERENCES LootTable(TableID) ON DELETE CASCADE ON UPDATE CASCADE,
    Probability DECIMAL(10, 4) DEFAULT 1 NOT NULL,
    IsUnique BOOLEAN NOT NULL DEFAULT false,
    DropsAlways BOOLEAN NOT NULL DEFAULT false,
    IsEnabled BOOLEAN NOT NULL DEFAULT true,
    DropAmountMin INTEGER DEFAULT 1,
    DropAmountMax INTEGER DEFAULT 1,

    PRIMARY KEY (ItemID, TableID)
);

As stated, these tables work in a non-recursive sense at the moment. I have a system that will query all LootTableObjects of a certain TableID (Each TableID represents a different monster's drop table). It adds any drops marked dropsAlways. Then adds up all probabilities, chooses a random number, and uses that to choose a regular drop. It loops again for however many drops the table says are to drop.

The problem I'm running into is how to conceptually model a database that would allow for sub loot tables. As an example, here is what I am trying to model:

A loot table would look like this:

LootTableObjects, amountOfDrops: 2
-----------------
Coins, Probability: 1
Health Potion, Probability: 1
Rare Drops Sub Table, Probability: 1

And the Rare Drops Sub Table would look like:

Rare Drops, amountOfDrops: 1
-----------
Steel Sword, Probability: 1
Steel Warhammer, Probability: 1
Steel Scimitar, Probability: 1

Therefore, if the sub-table was hit during loot determination, it would pick one of the 3 items in that table.

A sub table should have the exact same attributes as both the LootTableObject and LootTable. As in, I want the sub tables to have a certain chance to drop, and a certain amount of items that will drop when the table is selected, as well as if it drops always, or is unique (can drop only once during loot determination).

How would I change the LootTable & LootTableObject tables to account for these subtables? I'm more concerned with the concept of how this is done, rather than the exact SQL to make it work.

Answer 1

I implemented a custom loot system in a Neverwinter Nights server, which ran into several of the same concerns as you have. I also wanted the loot table to be modifiable by non-technical game-masters - so in my case, the loot tables were implemented as inventories on objects (which looked like actual tables, as a bit of humor) which the GMs could modify by adding items in and out of.

The way I handled this was as follows:

Placeholder Items: Tickets and Duds

For each loot table, there was a "ticket" item which, when spawned into a non-GM inventory, would be instantly replaced with a random item from its corresponding table's inventory. In addition, there was a "dud" placeholder, which would be instantly deleted.

The duds were needed because the loot drops were determined uniformly (which was necessary because of need to support modification by non-technical gamemasters using the game's default inventory UI). If a GM wanted to set a ticket to have a 10% chance of dropping an item, they would put that item and 9 duds into the table.

The players never see these placeholder items (neither duds nor tickets) because they are removed and replaced before the results are presented to the player.

Your tables are better than mine

Because you're using an actual SQL table, you don't have to deal with such contrivances - you'll rarely need to have more than one "dud" in each table, because you can increase the drop rate of the duds - though if you're drawing without replacement (see below), multiple dud entries will allow duds to be drawn more than once.

Guaranteed drops

Each lootable object in the game (e.g. monsters and treasure chests) should have a default inventory, which can contain items - including tickets. If you want to have an item that drops all the time (for example, a quest item with a 100% drop rate), have that item be included in the default inventory directly (and then make it invisible to players who aren't on the right quest, if appropriate). If you want a monster to drop items from a given table, put a ticket for that table in its inventory.

Unique items, Drawing with and without replacement

My system had a problem where all draws were done with replacement - I believe you can do better (since you aren't limited by the NWN client's UI as I was).

I would propose using the stack size column of your tickets to determine how many times to pull from the table without replacement - so a row with a ticket's ID and a stack size of 2 means 2 drops without replacement, while 2 rows with a stack size of 1 would mean 2 drops with replacement.

An example Boar

For example, you might have the following information regarding a typical Warcraft Boar.

• Its default inventory contains one boar ear (for a quest), plus a stack of two tickets for the "boar junk" table and one ticket for the "boar craftables" table, and one ticket for the "Level 15 generic magic items" table.
• The boar ear is only visible if the looting player is on the quest.
• The "boar junk" table contains a bunch of vendor trash items, like boar fur and broken tusks at various stack sizes, plus one "dud" entry for with a 40% weight.
• The "boar craftables" table has entries for things like meat, hide, and intact boar tusks which might be useful to players, plus one dud entry with a 50% weight.
• The "Level 15 generic magic items" table includes a 97% dud, with the remaining 3% being equipment drops for level 15 characters.

That would result in the following:

• Players who are on the right quest always get one boar ear per boar. (This is different from Warcraft in that there would only be one ear for the entire party, while WoW spawns an ear for each player - an alternative system may be considered for this).
• Players will usually see 1 item from the Boar Junk table (with the other roll being a dud), but 36% of the time, they will see 2 items from the Boar Junk table.
• Players will see 1 random boar craftable item 50% of the time.
• Players will see 1 level 15 equipment drop 3% of the time.

Answer 2

I was able to create a fairly overcomplicated but elegant solution to my problem through, basically, completely redoing the database. This solution takes after this article from 2012, and thus is my interpretation of that Random Distribution System (RDS).

Types and SubTypes

Firstly, instead of LootTableObject and LootTable being simple related entities, I created a parent type RDSObject, with several subtypes: RDSTable and RDSItemDrop. The SQL for these tables are:

RDSObject

CREATE TABLE "RDSObjectV2" (
    "ObjectID" SERIAL PRIMARY KEY,
    "ObjectType" INTEGER REFERENCES "RDSObjectTypes"("TypeID") ON DELETE CASCADE ON UPDATE CASCADE,
    "Probability" DECIMAL(10, 4) DEFAULT 1 NOT NULL,
    "IsUnique" BOOLEAN NOT NULL DEFAULT false,
    "DropsAlways" BOOLEAN NOT NULL DEFAULT false,
    "IsEnabled" BOOLEAN NOT NULL DEFAULT true
);

with sub-types:

RDSTable

CREATE TABLE "RDSTableV2" (
    "ObjectID" INTEGER REFERENCES "RDSObjectV2"("ObjectID") ON DELETE CASCADE ON UPDATE CASCADE PRIMARY KEY,
    "TableID" INTEGER UNIQUE NOT NULL,
    "Count" INTEGER DEFAULT 1
);

RDSItemDrop

CREATE TABLE "RDSItemDrop" (
    "ObjectID" INTEGER REFERENCES "RDSObjectV2"("ObjectID") ON DELETE CASCADE ON UPDATE CASCADE PRIMARY KEY,
    "ItemID" INTEGER REFERENCES Items("ItemID") ON DELETE CASCADE ON UPDATE CASCADE,
    "MinDrop" INTEGER DEFAULT 1,
    "MaxDrop" INTEGER DEFAULT 1
); 

and a small helper table to allow me to expand this as needed:

RDSObjectTypes

CREATE TABLE "RDSObjectTypes" (
    "TypeID" SERIAL PRIMARY KEY,
    "TypeName" VARCHAR(50) NOT NULL UNIQUE
);

This model allows me to specify any number of additional subtypes and assign them retroactively to any RDSTable, essentially letting me create any kind of drop table imaginable. Tables full of sub-tables, items, null values (Object Types come in handy here!), monsters, map segments, anything.

On the backend, I created a few endpoints to query the database and combine all the above tables to give my take on the RDS all of its functionality. As an example of what the backend might provide to the RDS in JSON format, here is an example output. Notice the difference between item drops, sub-tables, and null values.

The intent behind this specific table is for mining copper ore. There is a 1/31 chance of hitting a rare-ore sub-table that, when hit, selects a single random ore to drop from that sub-table. The JSON received from the backend processing the database query looks like this. Notice there are 3 RDSObject Types here, a Sub-Table, ItemDrop, and a Null Drop:

[
    {
        "ObjectID": 2,
        "Type": {
            "TypeName": "Sub-Table",
            "TypeID": 2
        },
        "Probability": "1",
        "IsUnique": true,
        "DropsAlways": false,
        "IsEnabled": true,
        "Table": {
            "TableID": 1,
            "DropCount": 2
        }
    },
    {
        "ObjectID": 3,
        "Type": {
            "TypeName": "Item",
            "TypeID": 3
        },
        "Probability": "10",
        "IsUnique": false,
        "DropsAlways": false,
        "IsEnabled": true,
        "item": {
            "itemid": 1001,
            "itemname": "Copper Ore",
            "stackable": false,
            "itemtype": 7
        },
        "Table": {
            "TableID": 1,
            "DropCount": 2
        }
    },
    {
        "ObjectID": 5,
        "Type": {
            "TypeName": "Null",
            "TypeID": 4
        },
        "Probability": "10",
        "IsUnique": false,
        "DropsAlways": false,
        "IsEnabled": true,
        "Table": {
            "TableID": 1,
            "DropCount": 2
        }
    },
    {
        "ObjectID": 4,
        "Type": {
            "TypeName": "Item",
            "TypeID": 3
        },
        "Probability": "10",
        "IsUnique": false,
        "DropsAlways": false,
        "IsEnabled": true,
        "item": {
            "itemid": 1020,
            "itemname": "Stone",
            "stackable": false,
            "itemtype": 7
        },
        "Table": {
            "TableID": 1,
            "DropCount": 2
        }
    }
]

From here, the backend can use this table to randomly select as many drops as the main table dictates it should. If a null drop is hit, one less item is simply returned to the client.

This solution is quite complex (at least in my fairly inexperienced opinion), but once all the queries are made, the tables populated, it's fairly intuitive and serves my purposes nicely.

I really appreciate the ease of implementation Tim C's answer would have, but I wanted something a little more robust, as many rules established in the database as possible, as oppose to putting that entire burden on the backend.

Is it perfect? No, but it gets the job done, and has a lot of room to improve down the line as the system gets updated more.