06-12-2020, 03:46 PM
Generally, places have kept all of the projects live together because database size is not really a factor. There are few tables that cause the size to balloon and you may wish to clean out periodically, namely, transaction_log, change_timestamp and ticket. I believe these often take up over 90% of the database space. Keeping everything live has advantages for reports and other diagnostics.
However, I understand that some way want to offline this. Every table that is needed in the sthpw database should be scoped with a "project_code" column, so you could export this if needed. I have indeed often questioned whether we should have put all the tables in each project (maybe this could be a enhancement in the future), but the reason for this was so that it would be easy to find a user's schedule even if they are working on multiple projects without thrashing every project in the system.
At any rate, you would need to export the following tables, scoped by the project code column: snapshot, file, note, status_log, pipeline, task
That's the ones I can think of offhand.
However, I am not sure this is worth it. I can understand if your tools are querying the entire table, but they should be scoped by project always or use appropriate filters. Also, if it is coming from an external API call that some technical director wrote in a script, it can be tricky to control because they can make an inadvertent huge query. How big of a problem is this situation?
However, I understand that some way want to offline this. Every table that is needed in the sthpw database should be scoped with a "project_code" column, so you could export this if needed. I have indeed often questioned whether we should have put all the tables in each project (maybe this could be a enhancement in the future), but the reason for this was so that it would be easy to find a user's schedule even if they are working on multiple projects without thrashing every project in the system.
At any rate, you would need to export the following tables, scoped by the project code column: snapshot, file, note, status_log, pipeline, task
That's the ones I can think of offhand.
However, I am not sure this is worth it. I can understand if your tools are querying the entire table, but they should be scoped by project always or use appropriate filters. Also, if it is coming from an external API call that some technical director wrote in a script, it can be tricky to control because they can make an inadvertent huge query. How big of a problem is this situation?