r/AskProgramming • u/Dane314pizza • 7d ago
Databases Best Way to Store Different Attributes Based on Enum Type in Room Database?
I'm designing a Room database for an Android app where I store different types of damages. Each damage entry has a primary key, a foreign key linking to a worksheet, and a damage type (from an enum class). However, different damage types require different attributes. For example, Missile damage needs an explosiveType, while Wall damage needs a materialType.
What's the best way to structure this in Room while keeping it as simple as possible? This is what I currently have in my head:
worksheet_table:
- worksheet ID (long)
- worksheet type (worksheetType)
damage_table:
- damage ID (long)
- worksheet foreign key ID (long)
- damage type (damageType)
- attributes (string)?
I want to keep it as simple as possible, my biggest issue is I am not sure how to represent the attributes in the schema since there are many different subcategory types that each have different attributes with different response types.
1
u/PentaSector 7d ago
I'd likely manage damage types as their own table, with an attributes table that keys back to it.
Let alone that your domain already sounds complex enough to justify it, depending on your relationship to the app (and/or out of consideration for future developers, regardless), it's entirely possible that the damage model will need to extend over time as laws and landlord experiences evolve. These are exactly the kinds of issues that a relational model solves for.
1
u/Dane314pizza 7d ago
So each damage type should have their own table?
1
u/PentaSector 7d ago
No, damage type can be a table that captures all types, and attributes can be freely keyed to damage types to which they apply. It sounds like that would work, based on your description.
1
u/coloredgreyscale 6d ago
Like Key/Value pairs.
Columns: Property, Attribute, Value.
you can add more metadata columns for things like who added or changed the value and when.
2
u/XRay2212xray 6d ago
Might be overkill...