Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

laracoft's avatar

Make sales records immutable

My sales orders records have a ship to address which points to an addresses table. The issue is these addresses are sometimes edited by users or admins etc and they don't fully understand it will also affect past sales orders.

Anyone has an elegant and normalized way to deal with this? Thank you.

0 likes
23 replies
Snapey's avatar

two options that immediately come to mind

  1. version control the address. each time the address is edited, create a new address record. Allow a customer to have many addresses but only the latest is 'current'. When creating an order the current address at the time is linked to the order

  2. give the order its own address which is initially populated by the customer address

Option.2 is probably safer, but you do need to give users the option to correct the address associated with the order in case they notice it is wrong. it would be embarrassing to still ship goods to the incorrect address after they think they have corrected it

Perhaps warn the user. "do you wish to apply this change to outstanding orders?"

jlrdw's avatar

Related data it's fine for some things, but certain data needs to be on each invoice also in case of these types of changes.

I've dealt with this due to IRS reasons many times.

Not only that but such data needs to be archived for several years.

Follow the programmers rule of thumb, never trust user input, and that includes supervisors input.

So have the sales records data that is subject to change due to a relation actually put on a sales record each time. Some business records need this for not only tax reasons, but other legal reasons.

  • Sally Johnson buys a product, the invoice has Sally Johnson.
  • she gets married and now her last name is Sally Wright.
  • she buys something else that invoice has her new name.

So old invoice should still say Sally Johnson, new invoice should say Sally Wright.

2 likes
laracoft's avatar

@snapey thanks for the inputs, it got me thinking:

Suppose I have a field called snapshot on the sales records, and it stores a JSON of the relevant info at point of creation, can it be used to flag out such issues to users and developers? What I'm driving at is can it be turned into a generic trait that can be used on all tables with such issues.

Side note: I really dislike the idea of creating multiple duplicate fields in the sales order, e.g. address line 1, line 2 etc like what some big ERP systems does, although I don't know if such a way brings any benefit

Snapey's avatar

@laracoft I would resist storing data in json

You should not be concerned by duplicated fields in this scenario, but you could use a polymorphic addresses table so it can store user addresses and order addresses in the same structure

laracoft's avatar

@Snapey

The JSON is only meant as a way to ascertain if this sales record has mutated... and possibly show the mutated parts?

SilenceBringer's avatar

@laracoft as @jlrdw said it's good practice to store all important information directly in the table, not via relationship. Not address only, but, for example, all prices and discounts should be stored with each order records, as far as they can change in the future, but this order must show the same price/discount on which it was ordered

martinbean's avatar

@laracoft Yup. Have dealt with this many times in e-commerce sites.

The crux is: don’t allow any order data to be changed at all after an order has been recorded. In the case of addresses, I’ve “synthesised” updating addresses. What I mean by this is, every time a customer updates their address via their account, I actually create a new Address model.

This is so that any orders that reference existing addresses via foreign keys, don’t suddenly get updated with new address data when a customer changes an address.

laracoft's avatar

Hi all, thanks for the inputs. Given that everyone who has done commercial work must have or will soon face this. Is there a package or something that can deal with this?

@martinbean I was pondering on your approach before the OP, what I could not figure out is, suppose we allow multiple addresses per buyer and this buyer is prone to making mistakes, thus has edited their addresses multiple times. How do I show only "valid" addresses and not those similar ones that were mistakes? Soft delete?

I really just wish there was a package for this, I'm happy to write one if I can nail down how it should behave. Thank you.

martinbean's avatar

@martinbean I was pondering on your approach before the OP, what I could not figure out is, suppose we allow multiple addresses per buyer and this buyer is prone to making mistakes, thus has edited their addresses multiple times. How do I show only "valid" addresses and not those similar ones that were mistakes? Soft delete?

@laracoft Yes, I soft-delete the old address (so it’s still available to other tables, i.e. orders, pointing to it via foreign keys) and then just create a new address.

So if a customer has two addresses in their account and updates one, they’ll still have two addresses in their account, but the database will have three rows (the two “active” addresses and the old, soft-deleted address).

laracoft's avatar

@martinbean

Cool, would you be so kind as to share implementation details? I suppose the only code changes all reside in the address model? Thank you.

laracoft's avatar

@martinbean

Suppose the table being synthesized is now the customers table. How would you maintain the $customer->id? It would be hard to maintain permalinks with a constantly changing customer ID.

Thank you.

jlrdw's avatar

Use quickbooks. Or some other sales or accounting package.

I have used Peachtree accounting, but many years ago.

Edit:

It helps some when you have done what you are programming, like payroll, accounts payable, accounts receivable, etc.

A suggestion only, if you have not had any, have you thought about taking a few business-related courses.

laracoft's avatar

@jlrdw

Hmm... I just asked myself, why do we want to keep sales records immutable? I think it is more to do with analytics and customer queries rather than for accounting purposes.

My past "solutions" for accounting was to post to an API and post further updates when there are refunds etc.

By package, I meant Laravel package such as a trait or something

jlrdw's avatar

@laracoft really a package isn't necessary, just example, but when certain fields on an invoice needs auto filled, I use a popup lookup table or dropdown can be used.

  • click on customer
  • have ajax fill the name, address, etc fields

But hard fill, not related data here in case as stated 6 months from now there's a change of information.

That way your related data is still always good for a current invoice being generated.

So yes you can write a custom method to auto fill several fields. But I don't think an add on package is necessary. But there probably are laravel sales, accounting type packages.

Just suggestions.

Edit: At the logistics company I worked for, there were times we had to pull up a load from several months back, especially if a claim or damage was involved. It was similar, each load (dispatch record) had it's data hard coded.

laracoft's avatar

@jlrdw

Sure, this is really an open ended discussion, I'm open to all constructive views with the hope of finding a good solution.

My reason for not wanting to create fields for hardfilling is the additional work it introduces when fields need to be added and removed. They have to be made on both or multiple tables, which requires knowledge of all related tables etc. Furthermore, your latest example shows that sales records are not the only ones that need this "protection".

Time and again, I have been amazed by packages out there that solves problems in a non intrusive way, e.g. spatie's laravel-medialibrary which allows multiple image attachments to any model with minimal effort.

So perhaps the description for this new package could be that it protects the integrity of a record and it's related records with minimal effort.

Snapey's avatar

@laracoft there are packages that will track model changes but that's for auditing, not your use case

You could make the address immutable by not providing an edit function at all- but I doubt that it's really what is required

Making the address model a polymorphic relationship of both the customer and the order then you can avail the issue of having slightly different address schema

What everyone is advocating is that the order gets a copy of the address at the point of order

laracoft's avatar

@snapey

I'm not questioning if there is a need to make a copy. I'm questioning if there are other ways apart from the 2 mentioned: maintaining the duplicated fields across tables OR synthesizing.

laracoft's avatar

@Snapey Sorry, but I can't connect how polymorphic addresses can prevent past records from being modified.

Snapey's avatar
Snapey
4 years ago
Best Answer
Level 122

If you have a table of addresses, the address can belong to a customer model or an order model.

When a user places an order, copy the customer address to a new address record attached to the order.

Now this order address cannot be modified without you providing some function to edit order address details. If someone modifies the customer address then this only affects new orders that will get a copy of the amended address record.

By doing it this way, you are avoiding maintaining virtually identical address tables and potential field inconsistencies.

alessandro.mirabelli's avatar

something like this?

cust 
  -cust_addr_id 
  -...

cust_addr
  -street
  -number 
  -...

order_addr
  -street (filled if cust_addr_id)
  -number(filled if cust_addr_id)
  -cust_addr_id
  -...

on front end a flag "use default addr", pre checked, if unflagged showing form inputs. i dont know the scale of your project if this is too much simple for you or not...

dcx's avatar

I know an old thread but for me, in the end this would be - user hasMany addresses and an order belongsToMany addresses (shippedTo & BilledTo) then soft deletes on address model so if user wants to delete an address all is fine...

Please or to participate in this conversation.