In a recent note we talked about moving historical records out of the main table into a history table or, depending upon the purpose of the historical records, an audit table. One of comments that I got back was that had a number of additional benefits:
- Easier to write code to retrieve data - no fancy date handling required
- Easier to use ad hoc reporting tools - same reason
- Better performance due to simplified date handling and smaller table sizes (as only most current record kept)
- Can control access to current vs historical data easily by restricting access to the various tables
- Easier to archive, as you only need to worry about the history table
(Thanks Rob)
It's easy to miss amongst the glitz and glamour of coming up with solutions that everything we do, every decision we make, has multiple ramifications. What we may do to "simplify" something may cause severe repercussions in other areas, totally negating the positive benefits. Sometimes we come across a solution that has both positive and negative impacts, but the positive impacts so far outweigh the negative that there doesn't seem to be a reason not to adopt the new approach.
Coming up with alternatives can be quite difficult, which is where "peer review" comes in really handy. Grab a friend or two, someone who has done some design work before, and show them your design. Help them understand the problems and the solutions that you've come up with. Peer reviews are tremendous tools in that they help to validate approaches and ensure that other possibilities have been considered. (Don't go overboard on documenting your design until after you've had a peer review, however, as the more time you invest in your solution the less likely you are to consider other options.)
No comments:
Post a Comment