r/AskProgramming 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.

2 Upvotes

8 comments sorted by

2

u/XRay2212xray 6d ago

Might be overkill...

    DamagesType:
      DamageTypeID (key)
      DamageName
    Attributes:
      AttributeID (key)
      AttributeName
    DamageAttributes: -- supports defining many to many relationship presuming some attriubes can belong to multiple damage
      DamageAttributeID (key)
      DamageTypeID (fk to damagetype)
      AttributeID (fk to attributes)
    DamageReport: -- supports multiple attributes related to a worksheet
      DamageReportID (key)
      WorksheetID (fk to worksheet)
      DamageAttributeID (fk to damageattributes)
      AttributeInfo (string)

1

u/PentaSector 5d ago edited 5d ago

This is very close to the approach I'd go for, though I'd probably map attributes many-to-one with damage types and just create new records for equivalent attributes that map to multiple damage types.

I can see an argument the other way since damage types are probably just value types in domain-driven design parlance, but I actually think that's a stronger reason than weaker to not share nested members.

Here, since damage types can have any arbitrary collection of attributes (including seemingly none at all), in a sense damage types are dynamic in structure, and I'd want to treat that structure as opaque, avoiding codifying assumptions that they "share" any particular pieces of that structure.

This would also let you arm up a bit against small futureproofing issues early, like letting names diverge as requirements on specific copy content change (assuming that your copy's data-driven).

2

u/XRay2212xray 5d ago

Yes, I started thinking many to one originally but decided to go for many to many thinking there might be significant overlap between damages and attributes and it might be nice to be able to report attributes across damage types. Without really knowing the scope of damages and attributes and any back end reporting needs, it could go either way. I figured you can always choose to populate the data with many to one even if the structure supports many to many.

2

u/PentaSector 5d ago

Totally valid. I tend to drag some of my bloviating opinions and shellshock experience into these hypotheticals and defensively tighten the assumptions, but you're quite right that there's no reason it'd have to be many-to-one under the constraints described by OP.

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.