Let's imagine we are creating a site, where people can sell anything to other people.
For example, we have two categories: Computers and Cars
We have some filters for categories: Memory, CPU, Mileage, Color
And we have values for these filters: 4GB, 8GB, AMD, Intel, 0-9999, 10000+, Brown, Black (people cannot just type their own values, they have to select them from a list)
Example Django code:
class Category(models.Model):
parent = models.ForeignKey('self', null=True, blank=True)
name = models.CharField(_('Name'), max_length=30)
class Filter(models.Model):
categories = models.ManyToManyField(Category, db_index=True)
name = models.CharField(_('Name'), max_length=30)
class FilterValue(models.Model):
filter = models.ForeignKey(Filter, db_index=True)
value = models.CharField(_('Filter value'), max_length=30)
So, filters related on categories, filter values related on filters. Now we have our tables:
categories:
id | name
------------------
1 | Computers
2 | Cars
filters:
id | name
------------------
1 | CPU
2 | Memory
3 | Mileage
4 | Color
categories-filters:
id | category_id | filter_id
-----------------------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 3
4 | 2 | 4
filter_values:
id | filter_id | name
-----------------------------
1 | 1 | Intel
2 | 1 | AMD
3 | 2 | 4GB
4 | 2 | 8GB
5 | 3 | 0-9999
6 | 3 | 10000+
7 | 4 | Brown
8 | 4 | Black
And now here comes the question - how should I make the Item model? Example code:
class Item(models.Model):
category = models.ForeignKey(Category, db_index=True)
name = models.CharField(_('Name'), max_length=30)
price = models.IntegerField(_('Price'))
But how to link it to filters and filter values properply in Django? I can create two Many2Many relations, but it will create 3 databases. However, it needs only two:
item:
id | category_id | name
------------------------------
1 | 1 | My computer
2 | 2 | My car
item-filter-filter_value:
id | item_id | filter_id | filter_value_id
------------------------------------------
1 | 1 | 1 | 2
2 | 1 | 2 | 3
3 | 2 | 3 | 5
4 | 2 | 4 | 8
So now in DB two items: My computer with AMD CPU and 4GB memory and My car with mileage 0-9999 and black color.
Which way is the right way to implement this logic?
You can customize the many-to-many table to match your suggested table. In your example it might look like this:
class Item(models.Model):
category = models.ForeignKey(Category, db_index=True)
name = models.CharField(_('Name'), max_length=30)
price = models.IntegerField(_('Price'))
filters = models.ManyToManyField(Filter, through='ItemFilter')
class ItemFilter(models.Model):
item = models.ForeignKey(Item)
filter = models.ForeignKey(Filter)
value = models.ForeignKey(FilterValue)
Just pay attention to the docs about how to access the through model fields and how this changes the related manager.