Temporal data with Rails / Active Record

I'm looking for ideas/information ab开发者_如何学Pythonout managing temporal data with Active Record (Rails). One example would be the emplyoment history (working 100% in january, but only 80% from february up to now). This proably would be easy to tackle with a traditional 'has-many :eployment_parts'. But there's another case where the user can plan something like a todo list, which the user can change over time (this todo is valid from Jan-March, another one is valid from Jan-April, and then with changed details from May to August).

I know, there's no silver bullet solution for these kind of requirements, but I'd like to collect here some ideas/docmentations/plugins about this topic. It looks like there hasn't been done much in this area for rails, at least nothing which got public.

Please, if you have an idea, link or thought, drop a short answer!

Links so far:

• Eric's random thoughts, blog entry about temporal data in rails
• Bi-Temporal PostgreSQL, helps managing temporal data in postgres (w/o rails)
• Richard T. Snodgrass's book "Developing Time-Oriented Database Applications in SQL" can be downloaded from his homepage (out of print)

---

we had the need to keep historical data of all changes to database records, and be able to query data as-of-time, without impacting performance of querying current data.

The solution we envisioned is a Slowly-Changing dimension type 2, with history tables. The implementation is a Ruby gem that requires PostgreSQL 9.3 and fits nicely in Active Record extending it with the temporal framework (e.g. Foo.as_of(1.year.ago).bars).

You can find the code here: https://github.com/ifad/chronomodel :-)

---

You need to detail what you want to do a bit more.

For example, what "resolution" do you need? Do you plan to record every worked hour of every day? Or just the average montly workload? Per week, maybe?

Also, what do you want to do with holidays, and sick days?

(EDIT - answering to the comment below)

In your case I'd go with a model similar to this one:

class WorkSlice < ActiveRecord::Base
  belongs_to :employee
  validates_presence_of employee_id, start_date, end_date, percentage

  def calculate_hours
    #employees might have different hours per day (i.e. UK has 7, Spain has 8)
    employee.hours_per_day * self.calculate_days
  end

  def calculate_days
    #days between start_day and end_day that are not sick days, holidays or weekends
    workdays = ... #this depends on how you model holidays, etc
    return workdays * self.percentage
  end
end

The only association you need is with "employee", as far as I know. A method on employee would allow you to see, for example, how "free" that eployee is on a given date.

---

"I know, there's no silver bullet solution for these kind of requirements,"

That does depend a bit on how silver you need your bullet to be.

Anyhow. You might find the following stuff interesting too :

(a) "An Overview and Analysis of TSQL2" by Hugh Darwen and C.J. Date. A link is on www.thethirdmanifesto.com in the "Papers" section at the bottom. (b) "Temporal Data and the Relational Model", a complete book by the same authors plus Nikos Lorentzos. Contains a different, complete, proposal plus very sound justifications why they believe that proposal to be better. (c) You can find an implementation called SIRA_PRISE, written by me, based on the ideas from (b), on shark.armchair.mb.ca/~erwin