By default, the linked docfields in a doctype are stored with all the read only fields picked up from the referenced doc type.
For example while storing the “purchase receipt” of a particluar item, the item code / id AND the item name is stored in the purchase receipt table.
This will cause two main issues:
The size of the database will be higher than what it should be
What if the item name is changed after a purchase receipt transaction is posted ? Will the purchase recipt table also be updated for all references of item ?
Basically, how are the foreign key constrainst implemented in erpnext?
Are we not sidlining the basic requirements of data normalization?
In the case of a transactional/document driven system like ERPNext, normalization is a tricky beast. Much information needs to be captured so that it does not change for future lookups. You might think that duplicating an item name 100 times for 100 sales orders is wasteful, but what happens to those if/when the name of the item is modified (due to lets say a rebranding), and I need to pull up a sales order from last year due to a dispute? Suddenly those 100 sales orders we have on record don’t match the ones in the customers/suppliers hands. I can’t reproduce the order. That’s a much bigger problem.
Thanks @jvermette - Just want to add that redundancy adds to simplicity in querying and performance too. The cost is ofcourse the additional complexity and its a tricky balance to maintain.
@jvermette, @rmehta thank you for your inputs. I absolutely agree with both your points of ensuring consistency in reports / records and ease of running queries and performance
But, in case it is required, is there a way in the frappe-bench platform to only store the primary key for a referenced record and not all the other data that was looked up and displayed as read-only at the time of data entry/ transaction.