Storing History in a Database

In regards to storing history within a database, is it better to use a DateEnd (Ex. 1) or a Duration (Ex. 2)?

Or please feel free to even suggest another approach that would be the most effective.

Are there other changes that I should make to one of these Examples if one proves to be the correct approach? DB being used is MySQL although I don't think it has a bearing on the approach he开发者_开发百科re.

---

There are two perspectives on this one - firstly, what's the business domain? In your example, you've used "subscription" - these are often sold as "monthly", "weekly", etc. All other things being equal, I prefer my database to align to business concepts when possible. You might even go so far as to create a "subscription_type" table, and derive the duration of the description from the type.

That often clashes with the need for your database to perform. From that point of view, I'd work out what the most common queries are going to be, and see if you can make your database design work with the minimal amount of type conversion or calculation possible. Finding all records where the subscription expires on a given date, for instance, is a lot easier (and probably faster) if you can ask for dateEnd < targetDate, rather than calculating the date by adding the duration to the start date.

---

If you have start date and end date, you can always (or should always be able to) compute duration. If you have start date and duration, you can always (or should always be able to) compute end date.

You can also record all three and enforce a row constraint to the effect that they cannot "mismatch".

However, one very frequent kind of usage of the "end date" datum, is to filter out rows that are "not current" : something like WHERE END_DATE > CURRENTSYSTEMDATE(). If you have that kind of usage, then it is probably not advisable to "leave out" the end date.

---

I would store the end date as opposed to the duration. You can calculate the duration when needed. Seems to make more sense storing measurement points instead of measurements.

---

Derived values such as duration don't need to be stored in the database.

Your approach to capture history of a row will work in some cases, but to truly catch all changes to all rows you will likely need another table, something like subscription_hist that will be updated by a trigger on insert and update of the subscription. Then you would have to keep track only of last_modified_date and last_person_changed_by. Everything else could be derived. This way you could see what happened to the row over time. I don't have any pictures to clarify, but this method, if implemented carefully would allow for full point-in-time recovery of data, as well as maintaining historical information.