# Need some help/advice storying History

**URL:** <https://www.ruby-forum.com/t/need-some-help-advice-storying-history/139775>\
**Category:** Rails\
**Created:** [June 6, 2008, 9:40pm UTC](https://www.ruby-forum.com/t/need-some-help-advice-storying-history/139775 "2008-06-06T21:40:28Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [June 6, 2008, 9:40pm UTC](https://www.ruby-forum.com/t/need-some-help-advice-storying-history/139775/1 "2008-06-06T21:40:28Z")

</div>

Hi guys, I need some help storing Medical Record history.

Basically I will need any change in a row of data to be stored some  
where. All I can think of is to copy the row and the change and re-store  
it in the database. Example:

fname: John  
lname: Smith  
Medical Record Num: 12312312

then they change the number to: 23434333

So now I need to save his old number, when it expired, the new number,  
and when it was activated.

Please any ideas would be great. Thank you!

---

<div class="post-metadata">

**Author:** ![squeegy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dec6dc/32.png) [@squeegy](https://www.ruby-forum.com/u/squeegy)\
**Post date:** [June 6, 2008, 11:41pm UTC](https://www.ruby-forum.com/t/need-some-help-advice-storying-history/139775/2 "2008-06-06T23:41:50Z")

</div>

Alex Williams wrote:

> Hi guys, I need some help storing Medical Record history.
> 
> Basically I will need any change in a row of data to be stored some  
> where. All I can think of is to copy the row and the change and re-store  
> it in the database. Example:
> 
> fname: John  
> lname: Smith  
> Medical Record Num: 12312312
> 
> then they change the number to: 23434333
> 
> So now I need to save his old number, when it expired, the new number,  
> and when it was activated.
> 
> Please any ideas would be great. Thank you!

I think your approach is very sound. Because you probably do not only  
need to record the changes. At some time someone will ask you "how did  
the persons data look at ".

One way to do this is create your table like this:

## records

id : integer  
ssn : string  
fname : string  
lname : string  
medical\_num : string/integer (whatever you need)

valid\_until: datetime (accept null values)

If your primary key would normally be “ssn” (Social Security Number),  
you should now instead define your PK as a compasite key, containing  
“ssn” and “medical\_num”.

When one or more fields are changed, make a copy of the record and  
insert the new data in this copy. In the original record, set  
“valid\_until” = now()

Selecting current data is easy:

select \*  
from records  
where ssn = ‘xxxxxx’ and valid\_until is null

Selecting data from a specific date/time:

select \*  
from records  
where ssn = ‘xxxxx’ and valid\_until \<=   
order by valid\_until DESC

Hope this helps

- Carsten