How to design an efficient database design?

[analog abyss]

I have a multilevel category for a ticket type so How can I create an effieient database design. Is the self-referential model is efficient or is there a better way to to that?

S.N | Details | Nepali | SAARC | International

1. | Ticket Entry Fee       | 150        | 1000      |  2000
   

---

2. | Vehicle Entry Fee
   

---

2.1 | For Luxury Road

2.1.1 | Car, Jeep, Bus | 100

2.1.2 | Bike, Scooter | 20

2.2 | For Business Drive

2.2.1 | Car - 4 person | 3000

2.2.2 | Jeep - 10 person | 5000

2.3 | Other roads except luxury | 1000

2.4 | Helicopter Hovering | 7500

3 | Documentary, Biography | 10000 | 50000 | USD 1500

    |  using drone                    | 25%     | 25%     | 25%

===================================================

Based on this table how can I design my database. Problem statement.

• The category can have title and its prices based on different country type
• It can have heading and under that heading there can be sub-heading, and those sub-heading can further have sub-heading or it can have its own pricing.
• The heading in photography has a sub heading that is dependent with it but can take a value in percentage or flat linked with it.
• The Pricing can also be in USD some times.

Untill now what I have thought of the database design is:

class Category(models.Model):
    title = models.CharField(max_length=255)
    parent = models.ForeignKey('self', null=True, blank=True, related_name='subcategories', on_delete=models.CASCADE)
    is_heading = models.BooleanField(default=False)
    is_percentage = models.BooleanField(default=False)

    def __str__(self):
        return self.title


class Pricing(models.Model):
    REGION_CHOICES = [
        ('nepali', 'Nepali'),
        ('saarc', 'SAARC'),
        ('international', 'International'),
        ('all', 'All Regions')
    ]

    category = models.ForeignKey(Category, related_name='pricings', on_delete=models.CASCADE)
    region = models.CharField(max_length=50, choices=REGION_CHOICES)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    currency = models.CharField(max_length=10, default='NPR')

    def __str__(self):
        return f'{self.category.title} - {self.get_region_display()} - {self.price} {self.currency}'

[unkempt jewel]

dipbazz, that command is disabled in this channel.

[noble haven]

I've never done a table like this, but I think I'd do it just the way you're doing it -- i.e. with an optional parent field that points to itself

you seem to have an implicit constraint, that might be worth enforcing, namely: if the parent column is not None, then each of the Nepali, SAARC, and International columns should be None

[analog abyss]

@noble haven Thank you for your feedback.

[true valley]

Sure, that looks fine to me. Maybe you want to use one of the tree libraries available for Django:
https://djangopackages.org/grids/g/trees-and-graphs/

A nullable parent foreign key doesn't protect your code from cycles (A is the parent of B and B the parent of A) so there's some value in using a library which handles a few of those more common problems.

Even though django-mptt is top rated there I would discourage people from starting new projects with it -- as its maintainer. More on that here: https://406.ch/writing/django-tree-queries/

[rare tartan]

django-mptt is a thing of beauty 💚

[true valley]

I have liked it a lot as well. The recurring consistency problems when modifying the database concurrently have made working with it not all that enjoyable (see https://github.com/django-mptt/django-mptt/issues?q=is%3Aopen+is%3Aissue+label%3A"Broken+Tree"), that's why I have marked it as unmaintained and switched to tree queries.

[rare tartan]

yes makes sense, we've used it exhaustively with organizational hierarchies and work breakdown structures that were synced from erp and did not need constant rebuilds beyond their initial creation; so it was less of an issue for us with inconsistencies.

Thanks for the write up though! I'll have a read.