Order addresses must be made deletable.

[eleanorshakeshaft]

Currently if a user changes their order address (delivery or billing) the system will add a new row in the order_address table. This makes reporting on order address details difficult without doing a query to find the MAX(address_id) for each order (much looping). Deleted_at and deleted_by columns need to created and the deleted_at time retrospectively added (deleted_by isn't important going back). Each time a new address is added for an order the old address mist be marked as deleted.

The address loader will need updating to set whether to load deleted items.

These are the queries I have written for setting up the new columns and getting the historical data:

ALTER TABLE `order_address` ADD `deleted_at` INT(11)  UNSIGNED  AFTER `created_by`;
ALTER TABLE `order_address` ADD `deleted_by` INT(11)  UNSIGNED  AFTER `deleted_at`;

UPDATE order_address
LEFT JOIN 
    (SELECT     
       address_id,
       (SELECT 
    MIN(created_at) 
       FROM order_address sub  
       WHERE sub.order_id = main.order_id AND sub.address_id > main.address_id 
       AND sub.type = main.type ) AS nxttime
    FROM order_address AS main) AS deleted_at 
    ON order_address.address_id = deleted_at.address_id
SET order_address.deleted_at = deleted_at.nxttime 

[irisSchaffer]

Is this for returns? Because other than maybe with returns, I don't really see why/how you would ever update an order address. You can always only have delivery and billing address on one order, so...

[eleanorshakeshaft]

An address can be changed within fulfillment by the admin.