Modify core tables vs custom tables and joins

Question

I'm aware that there are multiple different ways of adding a custom column to the order grid, from overloading the block to custom layout handle with event observer for loading data. My question is more about where this custom data should be stored. Historically speaking we have achieved this by adding custom columns to the sales_flat_quote, sales_flat_order and sales_flat_order_grid tables as appropriate and copying the data over using the fieldsets in the configuration.

This process works and has suited our needs to this point, however modification of core tables in this manner feels a little dirty. This approach could cause issues with later upgrades to Magento and multiple modules working in this manner could cause compatibility issues with third party modules. This seems especially concerning in the case of us deciding to distribute an extension.

The only other approach that springs to mind is if our Cool_Feature extension was to create it's own cool_feature_order_extra table, probably with a foreign key reference to the sales_flat_order table, that we can then join whenever we need the information.

1. Is there a alternative (neater?) approach which I've neglected?
2. Is there a 'Magento best practice' way of doing it?
3. Has anybody ran into any issues with either approach?

Answer 1

Without knowing exactly what your extension will do it's hard to say which direction I should recommend you go. But, generally, iI believe that it is best practice to extend and add columns to sales_flat_* tables, as evidenced by Magento's own extensions that they have implemented.

For instance, in Enterprise Edition instead of creating join tables for sales data on Enterprise_GiftWrapping, for instance, they extend sales_flat_order and sales_flat_order_item to include relevant gw_* fields to contain sales information as needed.

In my opinion there is little to no danger in making your edits to these tables on a 1.x install of Magento - unless, of course, you're wanting to support legacy Magento versions in which case sales data was EAV until ~1.4CE.

A potential argument against using join tables is that any join in the admin that keys against a flat table used for order placement (such as sales_flat_order) has the potential of affecting a lock upon that table which may cause unintended problems for high-volume stores.

I had an admin sort by customer_email in the order grid view (a custom extension) - the email address is unindexed and the sort was on > 300k rows. This caused 3 orders to fail with a lock wait timeout, but payments had been made and authorized in the gateway. What's worse is that the customers were then unable to complete their order subsequently due to duplicate transaction id numbers, a limitation of the gateway.

So, proceed with caution... but in my opinion you're clear to edit the core tables.

Answer 2

Here is how I usually do it.
When working on a "commercial" extension I try as much as possible not to touch the core tables, not because it would be a bad practice, but mainly because someone might not like my extension and would want to disable it. I usually include an 'uninstall' sql in the documentation and it seams safer to just 'Drop table' instead of 'Drop column'.
When working on a project I don't bother with new tables since the "extensions" I'm adding to the project usually are "there to stay".
This is my general approach for all entities, not just sales entities.
If it's worth something, I didn't have issues in any of the cases. I've even updated the Magento version and my tables or columns were still there without any errors showing during the upgrade.