Topic: database design and historical datas

Hi

I am having a struggle with managing historical datas
I have a table where When a field is change I record the change of one the field and wether this was done by a manual change or by a script(so automated). This change is later on use to make some logic decision in real time. I have 2 possible solution but have a hard time to pick and wonder is there is a better way to do it

let say the table is call  myfieldlog with field  ID, createTime, From , to, isManual

1)First solution would be to denormalized the table by add a field (Revision) that contain the value current for the current one and archive for the rest 

2) Second solution would be to maintain in that table only the current version and create a second table (myfieldloghistory) that contains the archives (so just a table with same fields) so that everytime a change is done, an update is performed on the myfieldlog table and a copy of the old data is pushed to the history table

What do you think is best as an approach?

What bothers me is to have to create 2 table every time I need some type of historical data . But I don t like the denormalized solution as usually denormalized data are harder to maintain(in terms of design evolving)

Thanks

Manu

Re: database design and historical datas

You might want to try the acts_as_versioned plugin. I believe this takes the same approach as your second option.

Railscasts - Free Ruby on Rails Screencasts

Re: database design and historical datas

Thanks for the answer. That helps a bit. But does that mean that it is a better pratice in general to use multiple tables?

Re: database design and historical datas

I think it depends on what you're doing. If you are displaying and accessing the history just as much as the current item (or both together), you probably want to keep it all in one table. But if you rarely access the history, it makes sense to keep it in a separate table so it's more out of the way and you'll probably have less performance issues.

Railscasts - Free Ruby on Rails Screencasts

Re: database design and historical datas

Thanks! I think that make sense.

Re: database design and historical datas

Hey Ryan

I did not realize you were Ryan from Railscasts smile What theme do you use for Textmate? I like your colorscheme

Re: database design and historical datas

You can find the textmate theme on the about page. smile

Railscasts - Free Ruby on Rails Screencasts