TTRPG and RDBMS
Dungeons & Databases
2024-02-07
Contents
- Introduction
- Recfile
- First Base
- Third Base
- Where It Starts To Break Down
- Conclusion
- Resources and Further Reading
Introduction
Two of my favorite things are tabletop role playing games and databases.
Games help me feel and experience things. And databases help me organize and express things. Both are a good way to learn new things!
So I recently started playing a new game with some new friends. And Iâm loving it! The game has a fair amount of complexity in terms of the number of player characters (every player essentially plays two characters: a main, and a powered up alt) and non-player characters (every player starts with connections to two NPCs), and the relationships between them all.
During all of our games so far (all two of them) there has been a point when I have forgotten the name of one character or another.
So I decided to make a database to refer to while playing to help me remember!
Recfile
My number one most favorite database is a recfile database. There are reasons for this:
Records in a database are plain text, key-value pairs. There is no difference between writing a slightly structured plain text note, and having a queryable database! The only friction involved in starting a new database is that of opening a text editor.
It allows incremental, iterable database design. When Iâm getting started on a small hobby database like this one, I sometimes have only a rough idea of what the data will be shaped like. So I just start slapping data into records and see what emerges. The records donât need to be uniform or complete at this stage. (This is the same advantage as document-based, non-relational, NoSQL databases.) Then once a schema starts to emerge, you can document and enforce it with record descriptors. Once I get to this stage, I usually find a few errors and discrepancies to correct. But then I have data integrity!
You can make it relational. You can normalize your data into multiple records. You can query it and perform joins. You have access to aggregate functions like Count(), Avg(), and Sum(). You can group entries. Itâs an actual, capable little database query language.
You can easily outgrow it. And you will outgrow it eventually for a project of any considerable size and complexity. For while it is for the most part perfectly capable, it is also quite minimal and not without limitations. No worries! Export to CSV and MDB is built in. And importing CSV into sqlite is a slice of cake.
In summary, it allows wicked fast prototyping. By the time youâve finished making notes to yourself about a possible data model, youâve already created a working database. You can update the schema quickly without tedious migrations. Itâs often âgood enough,â until it isnât, at which point you can easily migrate to a ârealâ database.
First Base
So letâs make a database.
## Example 1
player_name: braden
player_pronouns: they/them
character_name: Calliope
character_pronouns: she/her
alt_name: Verdade
alt_pronouns: they/them
npc_name: Isabela
npc_pronouns: she/her
npc_bio: ...
npc_name: Camryn
npc_pronouns: they/them
npc_bio: ...
Example 1 is a truncated version of a character record. The complete record contains more character info like bio, description, abilities, etc. Things that are actually related to the character. This is the kind of record that I might write when Iâm in the âjust throw data at itâ phase of capture / design.
At this point the character record is in fact kind of long. And
thereâs some info here that doesnât technically have anything to do with
the character. You can tell because of those prefixes like
npc_xxx
. They are logically grouped together (in an as of
yet theoretical ânpcâ group that doesnât exist yet) and probably donât
belong here in this record.
Third Base
There are guidelines for database design called Normal Forms. Some guy named Edgar made them up, and now everybodyâs crazy about them. Making your database adhere to these rules is called normalization, and it improves effeciency and reduces anomolies in your database.
Weâre gonna crank our database up to somewhere near 3NF, or third normal form, by ensuring that each character record entry is dependent only on the character itself and not anything else.
Right now alt_pronouns
depends on alt_name
.
And npc_bio
depends on npc_name
. Not on the
character itself. So these should be their own records.
Letâs go ahead and split them out:
## Example 2
%rec: character
%type: player rec player
%type: npc rec npc
%type: alt rec alt
name: Calliope Rodrigues
pronouns: she/her
alt: verdade
bio: ...
player: braden
npc: isabela
npc: camryn
%rec: alt
%key: id
id: verdade
name: Verdade the True
pronouns: they/them
%rec: npc
%key: id
id: isabela
name: Isabela Rodrigues
pronouns: she/her
bio: ...
id: camryn
name: Camryn Brighton
pronouns: they/them
bio: ...
%rec: player
%key: id
id: braden
name: Braden Soliloquy
pronouns: they/them
username: ...
Now we have four different records separated by record descriptors.
The record descriptors are lines starting with %
.
(e.g. %rec: npc
) The separate records are like separate
tables in a traditional SQL database. They can all be in the same file
like this, or they can be in separate files.
Now our data is normalized such that each key/value pair is related
to, or dependent on, only its own record. Or, more specifically, on its
record id. No more having to namespace keys with a descriptive prefix
like npc_xxx
. Because in the npc
record,
everything is about the npc! And in the character
record,
if itâs not specifically about the character itself, then itâs not kept
in that record.
Now if I want to see which character is played by Braden, and who their associated NPCs are, I can run the following query.
$ recsel db.rec \
-t character \
-j npc \
-e 'player = "braden"' \
-p name:character,npc_name:npc \
-G name \
-U
character: Calliope Rodrigues
npc: Isabela Rodrigues
npc: Camryn Brighton
A quick breakdown of the recsel options:
-t
: select a record of a certaint
ype. This is necessary now because we have 4 different types in our recfile.-j
:j
oin on field name. Requires the%type: <key> rec <other-record>
descriptor, and that the foreign record have akey
defined.-e
: a selectione
xpression-p
: keys top
rint. Note that usingprint:as
syntax, you can alias a key as something else when printing it.-G
: the key toG
roup by. (Else there would be a result for every successful join.)-U
: only showU
nique fields. (Else our result would have duplicate lines after Grouping records.)
Where It Starts To Break Down
I mentioned earlier that itâs possible to outgrow recutils fairly quickly.
Today, for example, I had just created this beautiful, normalized database for my game, and then wanted to combine all the data and report it out. I was quickly reminded that recsel canât do more than one join. This doesnât necessarily mean that itâs time to quit your recfile, but it does require a little finagling if you can tolerate it.
We will use recinf -d
to print the record descriptors
for records as we go through the following procedure.
- use
recinf -d
to print the record descriptors forcharacter
- join
character
withnpc
and print the results. (Now we have a brand newcharacter
record, complete with record descriptors, that includes the npc data.) recinf -d -t player
to print the player descriptors- print the
player
records withrecsel
- concatonate
Now weâve printed a new database of characters joined with npcs, and
the player database, each complete with record descriptors. Basically a
whole new recfile on stdout! Letâs pipe through another
recsel -j
to join those two records, combining character
and player. Now we have a double join: characters join npcs join
players.
Finally we repeat the whole process to join alt
.
recinf -d -t character
again to get our character descriptors again.- print the newly double-joined character database from stdin. (Now we have our whole character databse on stdout)
- print
alt
record descriptors - print
alt
records - concatenate everything
- join it
Bonus step: Notice at this point that cat
is printing
the output of our commands with no spaces inbetween, causing some
records to get weird and smooshed. Insert an echo
between
each command to insert a space after each output.
$ cat <(recinf -d -t character db.rec) \
<(echo) \
<(recsel -t character db.rec -j npc -G player -U) \
<(echo) \
<(recinf -d -t player db.rec) \
<(echo) \
<(recsel -t player db.rec) \
| recsel -t character -j player \
| cat <(recinf -d -t character db.rec) - \
<(echo) \
<(recinf -d -t alt db.rec) \
<(echo) \
<(recsel -t alt db.rec) \
| recsel -t character -j alt \
-p name:character,player_name:player,alt_name:alt,npc_name:npc
character: Winifred "Nif" Cecilia Lewison
player: Dot
alt: Concordia
npc: Helios Roth
npc: Poppy Gartner
character: Janus Quinn
player: xiis
alt: Cronos
npc: Splendiferous Cogwell aka Splendiferous Maximus
npc: Moonflower
npc: Max
character: Cary Mellan Evander Lyall
player: L
alt: Lykos
npc: Corvus aka Raven
npc: Rhys Abbott
character: Calliope Rodrigues
player: Braden Soliloquy
alt: Verdade
npc: Isabela Rodrigues
npc: Camryn Brighton
Conclusion
That was kind of a lot of work! It might be time to export this recfile into a sqlite database! Or, you know, maybe just search around in my plain text file.
Itâs not actually that bad if you just pop that huge cat-n-recsel into a script. Then youâll have the joined data at your ready whenever you want it. And in the meantime you still have some nice normalized data for some nice smaller queries.
In practice, I often arrive at this intermediate spot where using the recutils by hand is a little tedious, but also Iâm not quite ready to start using a ârealâ RDBMS yet. In which case I write some bash script to wrap inserting and updating records, and performing common queries. In fact I have a lot of lists and logs in this state that I export to html and publish on the web. Several small blogs of mine exist on my computer as a single recfile with a bash script UI for creating and editing posts.
Resources and Further Reading
- GNU Recutils https://www.gnu.org/software/recutils/
- recfiles: you wont believe this one weird database https://chrismanbrown.gitlab.io/28.html
- Database Normalization https://en.wikipedia.org/wiki/Database_normalization