What is the difference between these two dB tables?
I can see for the data, the sales_order_grid appears to be a subset of sales_order. Is this true? What is the purpose of having duplicate tables (I assume it is to prevent lock ups).
If I have a custom module by which I want to add a field to an order, which table do I need to add the field to? And how do I ensure the data is consistent?
sales_order_grid has less information about the order. It is basically used for Admin order listing section(Admin>Sales>Orders).
Where sales_order is main table and has much information. This data from table use on API, frontend order view, Order details admin etc.
So, if you want to add the new field to order, then you must need to add this new column or field to sales_order first, then if you want to show this field to grid then you can add this field to sales_order_grid.
Magento\Sales\Setup\SalesSetup::addAttribute is best method for add a column to order table and grid tablel
Data between grid and mail table is update using Asynchronous order data processing
sales_order_grid is refreshed from sales_order: medium.com/@sivaschenko/…