CODEWITHSUNDEEP
ruby-on-railsdatabase-design
Thomas Andrews
Thomas Andrews

Reputation: 1597

Rails design for database record (employment) history

So, I have a Rails application that tracks employees. We want to track the employment history over time. We'd like to be able to do the following sorts of queries:

  1. Search employees by their current status (salary, manager, title etc.)
  2. Search for remployees by status as of a particular date
  3. Search for employees who ever matched a condition

We don't mind the historical queries being significantly slower, even maybe 2 orders of magnitude slower.

This history is different from an audit trail - an audit trail is the history of the data, so an audit trail will tell you "what salary was stored for employee X on date Y," but it won't let you correct that older data if it is wrong.

[We are also using audit trails for auditing purposes, but I think of the logic for audit trails as providing an almost orthogonal design requirement.]

Is there a known database design pattern for best implementing this kind of history? Or a site where I can find discussions of the various "obvious" designs and the trade-offs? Any Rails plugins?

Rails 2.3.5 with Ruby 1.8.7, if that matters.

Upvotes: 3

Views: 407

Answers (1)

zetetic
zetetic

Reputation: 47548

I haven't used it, but paper_trail claims to support this type of version management. It would seem to handle your requirements (1) and (2) easily -- not sure about (3), you might need to do some heavy lifting to get a query that searched across models and versions at the same time.

This history is different from an audit trail

Meaning that you need the ability to make changes to old versions -- I doubt that is supported, and it seems problematic to implement if you also need this to act as an audit trail.

Upvotes: 1

Related Questions