0

Here's a simplified version of the data that I'm working with

{
    "name": "Sinistrofu Cloak",
    "itemType": "Cloak",
    "level": "200",
    "stats": [
      {
        "stat": "Vitality",
        "minStat": 251,
        "maxStat": 300
      },
      {
        "stat": "Strength",
        "minStat": 71,
        "maxStat": 100
      },
      {
        "stat": "Wisdom",
        "minStat": 16,
        "maxStat": 25
      }
}

And here's my simplified model:

class ModelItem(Base):
    __tablename__ = 'item'

    id = Column('id', UUID(as_uuid=True), unique=True, nullable=False, primary_key=True)
    name = Column('name', String, nullable=False)
    item_type = Column('item_type', String, nullable=False)
    level = Column('level', Integer, nullable=False)
    stats = Column('stats', JSON)

Most of the fields and how they translate into my database are pretty self evident. I'm really just curious about how I should go about structuring the stats column. Would it be better to store it as is in JSON format or would it be more proper to create a new stat model and create a new table for it that links back to the item model with a foreign key?

To me, it seems a bit unnecessary to do the latter but, as a novice, there may be benefits to it that I don't yet understand

Sam L.
  • 43
  • 1
  • 8

1 Answers1

1

It seems to me you should make a second stats table with a foreign key. This is the standard way to do this in SQL and allows you later to filter for items by their stats (which may be useful if you're looking, for example, for an item with a specific vitality).

SqlAlchemy also allows you to create relationships so you can immediately pull up the entire structure when you get a ModelItem rather than having to separately query or those stats or parse the JSON. There's good information on the SQLAlchemy docs.

It's worth the bit of extra work it takes to make the second model now so that later you can just get the full object hierarchy with minimal effort.

Alex Lowe
  • 110
  • 4
  • I guess the thing that made me question whether or not to do that was the nature of data. In my head, a `stat` didn't seem complex or distinct enough to warrant making another model. To me, it seemed like one aspect of a larger data type that is all the collective stats of an item. If that makes sense – Sam L. Nov 23 '19 at 04:55
  • @Sam L While from a business standpoint `stat` may not warrant a model, but from a technical perspective you should not (and probably cannot) store the `stat` entries as JSON. MySQL, for example, does not support JSON as native datatype (see [here](https://stackoverflow.com/questions/3564024/storing-data-in-mysql-as-json)), so you will store them in a text field. That makes things way more complicated than it needs to be. – Sirajus Salekin Nov 23 '19 at 05:20
  • Ah, I was using postgres which supports JSON. Good to know that it's not a feature of all RDBMS though. Knowing that though definitely helps cement making another table for the data. Thanks for the insight – Sam L. Nov 23 '19 at 05:44