We are going to start purging patron data from the transactions table, but we’d like to preserve some of the data about each checkout that we will be losing by breaking the link to the patron record, such as the patron’s age (rather than just their patron code), the patron’s census tract, or their notification option. It seems like the easiest way to do this would be to create our own table in the database to hold this data, but I wanted to check with some experts for advice, suggestions, tips, warnings, etc. I know the table will be deleted each time we upgrade, so we’ll need to figure out how to preserve it and recreate it, but are there other issues I’m not thinking of?
We’ve asked ourselves some similar questions around removing older transactions data, and how to back it up. We haven’t gone down that road yet, but key when we do will be capturing all the facets of the data that we want, and creating aggregated summaries that will allow us to still use the data similarly to reporting we’re already doing. It sounds like you’re already considering this, so I don’t expect that’s a new thought. However, I’ll mention that if you did want to be able to link to the patron record again, that would be possible if your aggregations included the PatronID - this would create more rows of data, but may be worthwhile if it’s important to relate details to the current patron record.
In terms of having a custom table, we have a lot in our system, and haven’t yet had them removed during an upgrade. As a precaution, though, we export all the table data before an upgrade - if you right-click on a database in SQL, under Tasks > Generate Scripts, you can export all your tables and data to a file that can be reloaded. Without going into too much detail, the important thing to remember with this approach is that on the “Set Scripting Options” step you’ll want to click on the Advanced option and for the "Types of data to script” option choose ‘Schema and data’.
Another approach you may consider is creating a separate database for custom tables. This would keep that data separate from the databases Polaris touches during an upgrade, which should add comfort. It’s not too complicated creating one, although you’ll want to think about where the data file is being stored and what size it may become in the future.
One last considerations with the custom tables: It’s useful having them all start with the same name so that they’re easier to find. We use our organization’s acronym, which helps for filtering, especially since most are in The Polaris database and use the Polaris schema.
Great advice all around @jtenter! We’ve also kept both separate databases and custom named tables for our own purposes in our SQL DB without issue. I think if we had to do it all over again, we would have started with the custom database ONLY approach as it does make it much less likely that an upgrade could wipe out your custom data.
The only consideration I’ll add is that you might consider offloading some of this data to a business intelligence product like Azure Data Lake, PowerBI or Tableau. Some of those reporting specific platforms can provide more flexibility in terms of reporting tools for stakeholders rather than relying on a relational database structure.