Is it better to store fk directly or get from another related table?

user_12428848 0
Viewed 115

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)

django-models django
3 Answers

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.

user_127785390

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.

user_142098130

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.

user_114356430
Related