'Duplicate the data OR saving Order Items data into JSONField
I have one to many Relationship with Order to OrderItem. OrderItems is saving the details of Item table. Item can be deleted by user, but I cannot delete the information of an item from OrderItem table ( Because it will used for Generating reports), so that is why I did not move the Item key to OrderItem and saving all the information of item table to OrderItem.
Now you can see that, data is being duplicated from Item to OrderItem table. My question is or I want to ask for suggestions that, i am using postgresql and we have luxury of JSONField. So is it a good idea to save all the information of Item table into JSONField and remove all fields?
Later on I have to generate reports from OrderItem, like how many Orders has been placed or calculate the profit using OrderItem table.
class Item(models.Model):
class Meta:
db_table = 'items'
SKU = models.PositiveIntegerField()
name = models.CharField(max_length=255, blank=False, null=False)
supplier = models.ForeignKey(Supplier,on_delete=models.CASCADE, blank=False, null=False)
purchase_price = models.DecimalField(max_digits=10, decimal_places=2)
trade_price = models.DecimalField(max_digits=10, decimal_places=2,blank=False, null=False , default = 0.00)
retail_price = models.DecimalField(max_digits=10, decimal_places=2)
class Order(models.Model):
class Meta:
db_table = 'orders'
customer = models.ForeignKey(Customer, on_delete=models.CASCADE, null=True, related_name='orders')
serial_no = models.CharField(max_length=30, null=False, unique=True)
recieved_amount = models.DecimalField(max_digits=15, decimal_places=3, default=0)
class OrderItem(models.Model):
class Meta:
db_table = 'order_items'
order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name='items')
name = models.CharField(max_length=255)
quantity = models.IntegerField(default=1)
purchase_price = models.DecimalField(max_digits=10, decimal_places=2)
trade_price = models.DecimalField(max_digits=10, decimal_places=2)
retail_price = models.DecimalField(max_digits=10, decimal_places=2)
if we are going to use JSONField for OrderItem the schema would look like this
NOTE Now relation of Order to OrderItem is also changed from 1:M to 1:1, another benefit as per my view.
class OrderItem(models.Model):
class Meta:
db_table = 'order_items'
order = models.OneToOneField(Order, on_delete=models.CASCADE, related_name='items')
item = models.JSONField(default=dict)
Format of the JSON will be like
[{
"items":
[{
"item_name": "Some product",
"quantity" : 3,
"purchase_price": 34,
"trade_price" : 54,
"retail_price" : 64
},
{
"item_name": "Some product",
"quantity" : 3,
"purchase_price": 34,
"trade_price" : 54,
"retail_price" : 64
},
{
"item_name": "Some product",
"quantity" : 3,
"purchase_price": 34,
"trade_price" : 54,
"retail_price" : 64
},
{
"item_name": "Some product",
"quantity" : 3,
"purchase_price": 34,
"trade_price" : 54,
"retail_price" : 64
}],
"sales_tax": [
{
"tax": "GST",
"percentage": 10
},
{
"tax": "FED",
"percentage": 4
},
],
"discount": 3
}]
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
