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
ghostmonkey
ghostmonkey11mo ago
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
ghostmonkey
ghostmonkey11mo ago
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…
Jochem
Jochem11mo ago
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
Jochem
Jochem11mo ago
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...
Jochem
Jochem11mo ago
(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
13eck
13eck11mo ago
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
Jochem
Jochem11mo ago
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
13eck
13eck11mo ago
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 sure
Jochem
Jochem11mo ago
you 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
13eck
13eck11mo ago
Here's my current table defs idea
No description
Jochem
Jochem11mo ago
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
13eck
13eck11mo ago
(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)
Jochem
Jochem11mo ago
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
13eck
13eck11mo ago
I figured it was better to store skill_name: 1 800 times then to store skill name: "Athletics" 800 times
Jochem
Jochem11mo ago
you've got a couple of instances where your foreign key field has _id at the end and a couple where it doesn't
13eck
13eck11mo ago
Damn, thanks
Jochem
Jochem11mo ago
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 me
13eck
13eck11mo ago
There'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
Jochem
Jochem11mo ago
that complicates things if you want an instance to support multiple settings too
13eck
13eck11mo ago
I guess I was planning on using the skill_name table as a user-contributed enum :p
Jochem
Jochem11mo ago
but 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_skills
13eck
13eck11mo ago
When 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)
Want results from more Discord servers?
Add your server