For example I have two models. What I get confused here is it will be better to store department_id directly on the Sale model or use product.department whenever you need it.
The use case for the department in sales model can be, filter sales by department, reports sales of department or so on.
class Product():
name = models.CharField()
department = models.ForeignKey(Department)
class ProductSale()
product = models.ForeignKey(Product)
department = models.ForeignKey(Department)
# other fields
Or just
class ProductSale()
product = models.ForeignKey(Product)
# other fields
Which query would be more efficient.
ProductSale.objects.filter(department_id=kwargs.get("department_id"))
Or
ProductSale.objects.filter(product__department_id=kwargs.get("department_id"))
I think second query is less efficient but also i think there's no need for storing department_id in the ProductSale model since we can get department_id through product.(Department will be the same of product for sale as well)
Both statements are correct, they depend on business logic.
Case 1
class Product():
name = models.CharField()
department = models.ForeignKey(Department)
It shows that the product belongs to which department.
| Product | Department |
|---|---|
| Book | Education |
| computer | Electronic |
Case 2
For your case. If you want to store the information of sold product by department wise.
class Product():
name = models.CharField()
# other fields
class ProductSale()
product = models.ForeignKey(Product)
department = models.ForeignKey(Department)
# other fields
This indicates the same product can be sold by different departments also.
I think second query is less efficient but also i think there's no need for storing department_id in the ProductSale model since we can get department_id through product.
If Department is fixed product-wise as like case 1 then yes no need for storing department_id in ProductSale. But if that is not the case then storing the department in ProductSale is the correct way.
You can use the queryset.query method in the shell to see the corresponding SQL query.
print(ProductSale.objects.filter(department_id=kwargs.get("department_id")).query)
print(ProductSale.objects.filter(product__department_id=kwargs.get("department_id")).query)
You will observe that the second query contains an additional Inner Join operation as compared to the first one. So the second query would be less efficient.
However, whether this matters in practice is another question.
Business logic is more important than query efficiency. ProdcutSale model should have department because you want to track sales for each department but why do you need department in Product model? A Product can be sold by many departments.