Probably very simple database table question

[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?

[eluser]bretticus[/eluser]
You're right. You can only practically modify one column with one table. Your history table gives you virtually unlimited history. That'd be my vote.

[eluser]munkeh[/eluser]
Thank you. I almost didn't post this because it seemed so "newbie", but it's nice to get confirmation (even on the simple things!)