SQL Table Definition
I'm trying to make a database for a TTRPG (Fate, if you're wondering and am trying to figure out how to handle one specific part of the database.
Fate characters have both stress (plot armour) and consequences (long-term harm). My issue is that stress is a numeric (a character has X number of stress boxes) and consequences are textual (it's a short phrase describing the injury).
I currently am thinking that I need 2 tables: one for stress and one for consequences (as their enherit datatypes are different) but is there a way to have one "vitals" table (the term used on the character sheet for both stress and consequences) so I don't need to do too many joins (I already have a table for skills, aspects, stunts, and basic character stuff like name, etc).
Thoughts?
22 Replies
I don't know if it is the best solution, but have you looked into 'polymorphic association'. This is what we are doing in the project I'm working on to let one table be the main table (vitals) and be associated with multiple other tables (stress, consequences, etc), without having to create separate tables for each type of association
Medium
What is Polymorphic Relationships and how they work ?
In the world of database design and management, sometimes we encounter situations that defy the usual conventions of relational databases…
if I'm reading the descriptions right, I think you can probably get away with sticking it all in one table. Stress seems to just be an integer column, maybe two if the maximum stress varies per character (one for current one for max). Consequences seem to be something you refer to but don't need to join on or use in any other database sense, given that it's a free text entry. I'd probably just use a JSON column and store an array of consequences in there
The whole video is worth a watch, but you can check out specifically the bit about JSON arrays here https://www.youtube.com/watch?v=QZBxgX2OWbI&t=880s
PlanetScale
YouTube
You don't need NoSQL (use MySQL)
To learn more about PlanetScale, head to https://planetscale.com/youtube!
MySQL has first party support for JSON documents and it can be a really nice escape hatch from the rigid structure of a relational database.
To learn more, check out some videos from our free course:
* https://planetscale.com/learn/courses/mysql-for-developers/schema/js...
(and ignore that it's on the PlanetScale youtube channel, he may mention it once or twice but all the info is purely mysql based)
The entire PlanetScale channel is a treasure trove of MySQL information btw, Aaron Francis has an amazing down to earth teaching style
Stress is…an interesting case because it's not just an int column.
It's:
* a string column for the type (base rules assume Physical and Mental, but a mage might have Magic stress or a billionaire might have Wealth stress)
* an int column for how many stress boxes (from 2 to 5)
* an int column for how many are used (though I might omit that bit for now)
Consequences have:
* a column for severity (minor, moderate, severe, extreme; prolly use an enum as the type never changes)
* a string column for the name/phrase
* a bool column for if it has started recovery or not
That seems a bit mismatched for one table, IMO. But I'm no SQL expert…hell, not even a SQL-middling. Just a SQL novice :p
hm, given that it's not something you'd be actively querying on, I might still use JSON for both... There's also not that much wrong with just having a very wide table, especially for smaller numbers (like, not hundreds of thousands of records), so having three columns each for stress and consequences isn't the end of the world.
it's all about balancing performance and ease of querying
JSON doesn't sound bad, but I'm considering doing this for my final project for my bootcamp…and they want "Real SQL Tables", none of this JSON/unstructured-nonsese
If I were doing it myself I'd do a
consequences field that's a JSON string for sureyou can structure the JSON if you want 😄 But the classic approaches are either going wide or using joins. I wouldn't recommend using a single table if the data types don't match perfectly though, and even then if the concept is different it should probably be a separate table if you're going the JOIN route
Here's my current table defs idea
the
invoice table in my invoice workflow software is 31 columns wide, and that's not too bad. I think the OCR processing project we had as a companion was 50+ wide(skills/skill_names are two different tables to have one normalized locality for skill names so as to not have to duplicate skill names for every character)
I'm not a big fan of putting 1-to-1 relationships in separate tables myself. It's just hiding table width, which you can do if you need to for performance reasons (like pulling a binary or long text column out of a large table that's being queried by people who don't know to how to write queries), but otherwise I try to avoid it
I figured it was better to store
skill_name: 1 800 times then to store skill name: "Athletics" 800 timesyou've got a couple of instances where your foreign key field has
_id at the end and a couple where it doesn'tDamn, thanks
Hm, yeah, that might be an edge case. If the skills can change, that's much nicer, especially if it's a user facing value. Otherwise you could use an
ENUM. I don't think there's anything wrong using those for user facing values, but it feels off for meThere's the default skill list of 19 skills but GMs can make new skills for their own setting, so a set enum wouldn't work
that complicates things if you want an instance to support multiple settings too
I guess I was planning on using the
skill_name table as a user-contributed enum :pbut yeah, that's a good use case for a
skill_names table, though I'd probably call it skills and call the many-to-many table character_skillsWhen a player makes a character they choose the skills their character will have. if it's not on the list the API adds it to the list (normalized to lowercase to avoid
Athletics, athletics, and AThletics being different skills)