Planning the database structure for the game knowledge base, which will be more efficient?

Question:

Foreword, there is a ready-made django site with a knowledge base for the game. Under the hood, categories in the knowledge base are implemented in separate postgresql tables with object description fields and a set of properties for comparison, which are different for each category.

So far, there are not a very large number of categories in the database, and, accordingly, it can be painlessly remade.

I want to unify objects in the database into one table with an object description field and etc., and move the properties and their values ​​into two separate tables. After that, just make the connection of the object with the properties and their values.

The question is, is it worth doing? And are there any other options for solving the problem?

upd.

This is the database schema I had in mind, I took it from the online store.

Category model, for example: Weapons (subcategory Swords), Potions, Armor, etc.

class Category(MPTTModel, ModerationBaseModel):
    """
    Category of item
    extend ItemBaseModel
    which has slug, name,
    description, keywords
    """
    slug = models.CharField(
        _("Slug"),
        default="",
        unique=True,
        max_length=250)
    name = models.CharField(
        _("Name"),
        default="",
        max_length=250)
    title = models.CharField(
        _("Title"),
        blank=True,
        default="",
        max_length=250)
    description = models.CharField(
        _("Description"),
        blank=True,
        default="",
        max_length=250)
    keywords = models.CharField(
        _("Keywords"),
        blank=True,
        default="",
        max_length=250)
    parent = TreeForeignKey(
        'self',
        null=True,
        blank=True,
        related_name='children',
        verbose_name=_('Parent'))
    image = models.ImageField(
        upload_to='category_images/',
        blank=True,
        default="",
        verbose_name=_('Image'))

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = _('Category')
        verbose_name_plural = _('Categories')

    class MPTTMeta:
        order_insertion_by = ['name']

Model for a Knowledge Base page, for example: "Frostmourne Sword"

class Item(ModerationBaseModel):
    slug = models.CharField(
        _("Slug"),
        default="",
        blank=True,
        db_index=True,
        max_length=250)
    name = models.CharField(
        _("Name"),
        default="",
        max_length=250)
    title = models.CharField(
        _("Title"),
        blank=True,
        default="",
        max_length=250)
    description = models.CharField(
        _("Description"),
        blank=True,
        default="",
        max_length=250)
    keywords = models.CharField(
        _("Keywords"),
        blank=True,
        default="",
        max_length=250)
    image = models.ImageField(
        upload_to='Itemimeges/',
        blank=True,
        default="",
        verbose_name=_('Image'))
    category = models.ForeignKey(
        Category,
        on_delete=models.CASCADE,
        related_name='categories',
        blank=True,
        null=True,
        verbose_name=_('Category'))
    description = models.TextField(blank=True,)
    full_text = models.TextField(blank=True,)

    def save(self, *args, **kwargs):
        if self.category:
            super(Item, self).save(*args, **kwargs)
            # we create properties if not exist
            for cp in CategoryProperty.objects.filter(category=self.category):
                pp = ItemProperty.objects.filter(category_property=cp,
                                                 Item=self)
                if not pp:
                    pp = ItemProperty(category_property=cp,
                                      Item=self, value="--")
                    pp.save()

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = _('Item')
        verbose_name_plural = _('Items')

A model for a set of properties in a certain category, for example: Category Swords – properties of damage, speed, dps, etc.

class CategoryProperty(ModerationBaseModel):
    name = models.CharField(
        _("Name"),
        default="",
        max_length=250)
    category = models.ForeignKey(
        'Category',
        on_delete=models.CASCADE,
        related_name='categories_property',
        verbose_name=_('Category'))

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = _('Category property')
        verbose_name_plural = _('Category properties')

And the last model for storing the values ​​of the properties of a particular object, for example: "Frostmourne" – damage – 100, speed – 20, dps – 500.

class ItemProperty(ModerationBaseModel):
    category_property = models.ForeignKey(
        CategoryProperty,
        on_delete=models.CASCADE,
        related_name='category_property',
        verbose_name=_('Propery'))
    value = models.CharField(
        _("Value"),
        default="",
        max_length=250)
    Item = models.ForeignKey(
        'Item',
        on_delete=models.CASCADE,
        related_name='properties_Item',
        verbose_name=_('Item'))

    def __str__(self):
        return self.value

    class Meta:
        verbose_name = _('Item property')
        verbose_name_plural = _('Item properties')

But it turns out that if there are 100 pages in a category and each page has 10 properties, 100 * 10 = 1000 records in the database come out, plus 10 other categories 1000 * 10 = 10000 records (with the current implementation it will be just 1000 records and 10 different models for each category).

But with the approach with unified pages and separate properties (which I described above), all data management is carried out from the admin panel and adding new categories does not need to edit the model code. So my question is, is it all worth it?

And I also know store implementations where product categories are also hardcoded in the models, here is an example Stub for a django-SHOP store

I take stores as an example, since the knowledge base is the same store only without a basket =).

Answer:

Yes, everything is so, the point is that to add a new category, you need to create a new model, do migrations, etc.

Not necessary. You make a model, for example, "potions".

class Зелье(models.Model):
     название = CharField()


class Свойство(models.Model):
    свойство = Charfield(choices=типы_свойств)
    процент_изменения = FloatField()
    зелье = ForeignKey("Зелья")

Well, that's all. Then you create these potions from the admin panel, as much as you like.

Scroll to Top