How can I enforce ensure uniqueness of an association with a time dimension?
I was thinking because this was a calculation it'd have to be a trigger but in doing some googling there are better options.

Overlapping intervals look like they can be blocked by a particular pattern of constraints (http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx)

This slideshare walks through 'Exclusion' constraints which are part of PostgreSQL 9.0 and above and also touches on the pitfalls of approaches like triggers (http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints)

This post titled 'Non-Overlapping Dates Constraint' covers several approaches for Oracle (http://jeffkemponoracle.com/2012/08/30/non-overlapping-dates-constraint/)

My Google search query was 'unique time interval constraint database'