Database Design: Similar offerings/services from different providers that have varying prices

Question

I am building an API using Django/DRF and Postgres for an application that takes reservations for spas, salons etc.

I expect to have a large number of providers (spas) who provide a menu of similar services, but at differing prices.

Provider 1 Menu
---------------
Pedicure ($50)
Manicure ($40)
Thai Massage ($100)
Balinese Massage ($120)

Provider 2 Menu
---------------
Pedicure ($60)
Manicure ($50)
Head Massage ($120)

...

Provider 35000 Menu
---------------
Pedicure ($25)
Manicure ($25)
Balinese Massage ($90)

I am trying to identify the appropriate models to represent this situation

This is what I have come up with:

class ServiceCategory(models.Model):
    Category = models.CharField(max_length=100, blank=True)

    def __unicode__(self):
        return self.Category


class Service(models.Model):
    Category = models.ForeignKey(ServiceCategory)
    Name = models.CharField(max_length=100, blank=True)

    def __unicode__(self):
        return self.Name


class Provider(models.Model):
    Manager = models.OneToOneField(User)
    Category = models.ForeignKey(ServiceCategory)
    Name = models.CharField(max_length=100, blank=True)

    def __unicode__(self):
        return self.Name


class MenuItem(models.Model):
    Provider = models.ForeignKey(Provider)
    Service = models.ForeignKey(Service)
    Price = models.DecimalField(max_digits=9, decimal_places=2, db_index=True)

    def __unicode__(self):
        return self.Provider.Name + "-" + self.Service.Name

How will this structure perform to serve queries of the following types:

1. Retrieve the entire menu for any one provider, with prices
2. Retrieve a list of all providers (filtered by geo, sorted by price) that provide a class of services (say Massages)
3. Retrieve a filtered list of all providers that provide a specified service (Thai Massage)

---

Intuitively I am concerned about the table for MenuItem growing huge and slow in no time, say 1 to 4 million rows, that too accessed by AJAX and Mobile Apps via the API.

What would be a better way to model this for performance?

Maybe de-normalization, PostgreSQL settings, (mem)caching, or breaking up the table?

Answer 1

1. Retrieve the entire menu for any one provider, with prices

   my_provider = Provider.objects.get(pk=my_provider_id)
   items = MenuItem.objects.filter(Provier=my_provider)
   for item in items:
       print item.Service,item.Price
   

2. Retrieve a list of all providers (filtered by geo, sorted by price) that provide a class of services (say Massages)

   Doing a reverse lookup like that will be hard, but you can a relatively efficient lookup like so:

   my_ids = [i.Provider.pk for i in MenuItem.objects.filter(Service__ServiceCategory=my_category)]
   providers = Providers.objects.filter(geo_filter='do it here').filter(pk__in=my_ids)
   

3. Retrieve a filtered list of all providers that provide a specified service (Thai Massage)

   my_providers = [i.Provider for i in MenuItem.objects.filter(Service=my_service)]