PayHistID creates a 1 to 1 relationship between SalesOrders and PayHist. This may work for one single orders with one item and one payment, but real world use case demonstrates orders may have multiple transactions including failed authorizations, credits, refunds , errors and/or other adjustments.
This fundamental design issue should be corrected, especially for integration with other management software or accounting systems.
Benefits
Allows multiple payment transactions per order
Records multiple error codes from failed transactions
Customers and employees can view related transactions in a single place without hunting through other systems
Customers statements will match online transactions
Maintains actual order sequence for audit trail
Prevents duplicate/phantom orders from failed transactions
Affects
Smith.BuyNow
Smith.MyAccount
Database
Add column OrderID to PayHist
Update OrderID from StoreOrders using PayHistID
Drop column PayHistID from StoreOrders
Drop column CustomerID from PayHist (unnecessary duplication, unless needed elsewhere)
Business Logic
Literally modify PayHistID to OrderID as required
User Interface
Ability to create additional payment records
Add [PAYMENTDETAILS] token for emails, invoices, etc.