Maintaining historic data

Hi all,

Hope you can help! I'm just looking for ideas really on maintaining historic data. I'm building a system that'll act over the years as an archive for race series. As per the graphic below, a profile is linked to a team, and a profile to a result row, in turn, results themselves.

This is great and while the person continues to compete in that particular race series it's not a problem. But what about if the profile changes team, how can I ensure this data is persisted and when looking at old race results I can still see the team the rider belonged to at that point?

database schema

MThomas

6 years ago

Level 35

You might be able to accomplisch this by logging the changes to the model. Spatie has an excellent package for this: https://github.com/spatie/laravel-activitylog

What it does, if for example the team_id on your Profile model changes, it will record the change and the date the change took place. With that you should be able to determine what the correct data is when you're displaying historic data.

chrispage1

6 years ago

Level 14

Thanks @mthomas - I did actually take a look at packages like this. My concern with this approach is the speed of retrieving the data. It's critical (as this will be forming the base of an API) that the data can be retrieved quickly.

I almost wonder if when I create a result I need to create a snapshot of the data at that point in time. This way I can guarantee when a result is loaded in the future, it has associated every profile at that point in time, quickly and efficiently.

Please or to participate in this conversation.