I think you need to rethink your insert only design. If the goal is retain a history of all modifications to the account so that you can view all past versions then just create a new 'revisions' or 'log' table.

User_History
PK id : auto incrementing integer
user_id int --references user table--
... <Personal Data about the User>
created_at : datetime
/*updated_at : datetime THIS WILL NEVER BE UPDATED*/

userHistory Model Relationships

userHistory hasOne(User)

user hasMany(UserHistory)

Everytime a user saves something on the account lookup the user model. Write it's data to the log table, then update the user account. You will have a copy of all past versions and you can forget about chasing down all the relationships, that seems overly complicated.

I did do some rethinking, and I've come up with a better design.

First of all, I don't want a duplicate table for each Model that needs to have a history. I'd also don't want a single table to handle all of it, because that requires some iffy relationships that the database can't enforce.

So, here's the revised design: All tables that require a history will have the following columns:

• created_at: When the row was created or modified (for historical rows)
• updated_at: When the row was recently modified
• deleted_at: When the row was deleted or made obsolete (for historical rows)
• current_id: The FK to the current version of this row (for historical rows only, NULL otherwise)
• created_by: The user associated with created_at
• updated_by: The user associated with updated_at
• destroyed_by: The user associated with destroyed_at

So in all, there's seven columns to track historical information.

For current rows, the timestamps act as you'd expect. For historical rows, the created and deleted timestamps indicate when the row was current. The updated timestamp here is for when a change that is not historically relevant occurs.

To query the history of a row, you could probably use the following:

<Model>::where('current_id', '=', $id)->orderBy('created_at', 'asc');

This should result in all of the old revisions in order, starting with the original version.

Now to explain how to handle the default changes...

From what I've seen, only the update command needs to be overridden. Basically, it's going to grab the original attributes before the change, and make that a new row in the table. The primary key of the current row never changes.

When a row is being changed for the first time, the timestamps change as follows:

LHS is historical, RHS is current

• created_at = created_at
• updated_at = updated_at
• deleted_at = NOW

When a row is being changed again, the timestamps change as follows:

LHS is historical, RHS is most recent historical or current

• created_at: deleted_at (most recent)
• updated_at: updated_at (current)
• deleted_at: NOW

Note: If the updated timestamp is earlier than the created timestamp, then the updated timestamp is changed to NULL.

So, I'm not 100% certain how to code this functionality, but once I've taken a crack at it, I'll post what I have.

Your idea isn't a bad one and I understand you not wanting a 'history' model for each datatype you want to retain history on.

Somethings to consider

1. You don't necessarily need a history table per model, you can have one history table and create it in a generic way so that all models can write their history to it.

tblHistory

id | orig_val | new_val | model_type | updated_by | etc... | etc...

I am not suggesting you go that way, but it is something to consider.

2. If you are going to keep all the historical records in a single table with the live records, do some estimates of production row size. If you plan on having any searching features against 'live' data and that needs to ignore the 'historical' data then your queries are going to get bogged down sorting the historical records from the live ones. This isn't for sure going to be a problem for you, but if the historical records outgrow the live ones at a factor of 50 to 1 or even 10 to 1 you are going to take a performance hit searching through live records whereas when the 'history' lives in a separate table your query execution times for the 'live' data will be significantly improved.

Having a backup table for each model may seem like overkill but once it is all set up you will be left with a more elegant and normalized database... but for your needs this may not even be a concern, I just wanted to mention it so you can start thinking about what you getLiveData and getHistoricalData queries will look and perform when everything is in the same model vs everything in it's own model.

From what I've been told as far as this application goes, most of the information won't ever get modified.

Also, seeing that my design doesn't conflict with soft deletion (in fact, I already planned on using it), using the stock Laravel functions should help clean this stuff up. I'll probably using scoping as well to help with querying the 'trash'.

Lastly, my only issue with having a 'historical' model for each model is that I'd want to be able to have the models and migrations synch automatically, meaning I want to avoid any extra boilerplate.

As far as this application goes, we'll be using my revised design. However, I'm still going to look into this, because I still plan on using Laravel after the project, so having certain features figured out ahead of time would be nice.