> name | state | from_date | to_date
> ------------------------------------
> foo | state2 | <date> | <date>
> foo | state1 | <date> | <date>
> bar | state4 | <date> | <date>
> ...
> foo | state3 | <date> | <date>
I would consider using only 1 table, where a NULL to_date indicates the
current state, and you have a compound index on (name, to_date) to support
querying for current state. Perhaps you could use a partial index as well to
speed up those queries, but I haven't yet explored that feature of
PostgreSQL.
--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice