With any good database design we should not only consider the beginning but we should also consider the end. Effective planning should always take into account the life expectancy and removal of data. A past client thought they were sitting on an archived wealth of valuable contact information that had been transferred and stored for several years over several systems. However upon closer inspection during their most recent data migration it was discovered that most of their information was useless. Imagine the disappointment and the waste of resources needed to determine this.
The Government of Canada only requires business records to be kept for period of 7 years. Storing data past this time frame may be necessary for some companies but it should never be kept in the active working database file. It is far easier to:
a) Assign an Expiry or Inactive Date for records and then run a periodical Delete Query that finds those records and removes them permanently. This ensures the data is always current and useful and frees up valuable storage space that helps improve the performance of the database.
b) If data is required to be stored for longer periods of time a Copy should be made of the database, given a useful and informative file name like “2015 Archive” and stored somewhere safe both on a physical and virtual drive. Once that’s been completed a database scrub (Delete Queries – see option a) should again be initiated on the active working file.
The key for either process to be successful is to plan out the Expiry/Inactive dates into the records when creating the Tables. This can be accomplished either through a User Input Field or using back end House Keeping Fields.
If you’re not familiar with House Keeping Fields you can build 4 useful fields in every table that update automatically in the back end away from the user interface. (User’s won’t even know they’re there) They are as follows:
- Creation Date
- Created By
- Last Modified Date
- Last Modified By
Having automated time/user stamps for every record can help enormously with Database Archival and Clean up. Being able to envision a project not just from start to launch but also to the finish is the mark of a good designer. A well thought out design is easier to manage and to upgrade, so it’s definitely worth the extra work up front.
That being said and on a little personal tangent, perhaps this philosophy should be adapted in a broader sense for any company. When saying things like “improving operational efficiencies” we all recognize the inherent value of this concept. Doing more with less. When designing or building products what if we considered their life span and disposal? The alpha and the omega? Charles Goodyear spent years trying to discover the process of vulcanizing rubber successfully. Imagine the world if he had also taken the time to figure out what to do with the rubber once it was no longer needed. We are making astonishing leaps and bounds in technology and innovations every day. However perhaps we should also be looking at what comes after the finish line as we seem to race towards it at ever growing speeds.
The Government of Canada only requires business records to be kept for period of 7 years. Storing data past this time frame may be necessary for some companies but it should never be kept in the active working database file. It is far easier to:
a) Assign an Expiry or Inactive Date for records and then run a periodical Delete Query that finds those records and removes them permanently. This ensures the data is always current and useful and frees up valuable storage space that helps improve the performance of the database.
b) If data is required to be stored for longer periods of time a Copy should be made of the database, given a useful and informative file name like “2015 Archive” and stored somewhere safe both on a physical and virtual drive. Once that’s been completed a database scrub (Delete Queries – see option a) should again be initiated on the active working file.
The key for either process to be successful is to plan out the Expiry/Inactive dates into the records when creating the Tables. This can be accomplished either through a User Input Field or using back end House Keeping Fields.
If you’re not familiar with House Keeping Fields you can build 4 useful fields in every table that update automatically in the back end away from the user interface. (User’s won’t even know they’re there) They are as follows:
- Creation Date
- Created By
- Last Modified Date
- Last Modified By
Having automated time/user stamps for every record can help enormously with Database Archival and Clean up. Being able to envision a project not just from start to launch but also to the finish is the mark of a good designer. A well thought out design is easier to manage and to upgrade, so it’s definitely worth the extra work up front.
That being said and on a little personal tangent, perhaps this philosophy should be adapted in a broader sense for any company. When saying things like “improving operational efficiencies” we all recognize the inherent value of this concept. Doing more with less. When designing or building products what if we considered their life span and disposal? The alpha and the omega? Charles Goodyear spent years trying to discover the process of vulcanizing rubber successfully. Imagine the world if he had also taken the time to figure out what to do with the rubber once it was no longer needed. We are making astonishing leaps and bounds in technology and innovations every day. However perhaps we should also be looking at what comes after the finish line as we seem to race towards it at ever growing speeds.
RSS Feed
