[eluser]munkeh[/eluser]
First off, sorry for the potentially "duh" question.
I'm writing a little app to keep track of some insurance data. A claim can be in one of the following states: 'open', 'closed', 'pending', 'denied', and also has some related text notes. That bit is fine - I can do it any number of ways.
However, I'd also like to keep track of the date on which the claim status changed. My first thought was to just add a few more fields 'open_date', 'closed_date' etc... but that limits me to only recording the last time a claim entered a specific state - I'd get no historical data.
My next thought was to add a claim_history table that looked something like:
Code:
claim_history
-------------
claim_id
new_status
comment
date
That way I can just query for a particular claim_id and get a list of status changes. Would that be the normal way of doing things?