M
M
MoDPhoenix2017-11-04 22:53:57
PostgreSQL
MoDPhoenix, 2017-11-04 22:53:57

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

Foreword, there is a ready-made site on django with a knowledge base on 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 the objects in the database into one table with the object description field, etc., and put 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 it? And are there any other solutions to the problem?
upd.
Here is the database schema I had in mind, I took it from an online store.
Category model, for example: Weapons (Swords subcategory), 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: "sword Frostmourne"
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')

Model for a set of properties in a certain category, for example: Category Swords - properties 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, there are 100 * 10 = 1000 records in the database, and 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 done 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 the implementation of stores where product categories are also hardcoded in the models, here is an example Preparation for the django-SHOP store
I take stores as an example, since the knowledge base is the same store only without a basket =).

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
Stalker_RED, 2017-11-05
@Stalker_RED

And are there any other solutions to the problem?
Move everything to wikia.com
Examples:
fallout.wikia.com/wiki/Fallout_Wiki
en.left4dead.wikia.com/wiki/Left_4_Dead_2
en.rimworld.wikia.com/wiki

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question