💾 Archived View for tilde.town › ~dozens › gemlog › 21.gmi captured on 2024-05-10 at 11:03:07. Gemini links have been rewritten to link to archived content
⬅️ Previous capture (2024-03-21)
-=-=-=-=-=-=-
Dungeons & Databases
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!
My number one most favorite database is a recfile database. There are reasons for this:
1. 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.
2. 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!
3. 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.
4. 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.
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.
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:
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.
1. use `recinf -d` to print the record descriptors for `character`
2. join `character` with `npc` and print the results. (Now we have a brand new `character` record, complete with record descriptors, that includes the npc data.)
3. `recinf -d -t player` to print the player descriptors
4. print the `player` records with `recsel`
5. 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`.
1. `recinf -d -t character` again to get our character descriptors again.
2. print the newly double-joined character database from stdin. (Now we have our whole character databse on stdout)
3. print `alt` record descriptors
4. print `alt` records
5. concatenate everything
6. 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
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.
- 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
title: TTRPG and RDBMS posted: 2024-02-07 url: gemini://tilde.town/~dozens/gemlog/21.gmi keywords: database recutils contact: dozens@tilde.team