Přihlásit se
Database Design of loot tables
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.
