Need some help/advice storying History

Rails
1

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!

2

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