OK, I have this design problem I've not been able to find a good answer for.

Let's say you're tracking customer pricing for a widget. Now, widget prices are volatile, changing weekly or even daily. And you need to be able to go back in time and say "on this day, we would have charged you 5.32 yaks for a widget". So, you set up a table that has a price, a "price start date" and a "price end date". When you quote a price, you need a date, and you can retrieve the price.

So, a create table would look like this:

Price_Start_Date datetime not null,
Price_End_Date datetime not null,
Price decimal(9,2)

For a non-expired price put in an arbitrary future date, and when the price expires update the expire date. That way you get to use between (and avoid funky "isnull" comparisons everywhere).

Here's the design problem: Put a real primary key (not a surrogate key) on that table.

I can think of some really ugly solutions (involving tables of every conceivable date and an interesect table). Or refining that going all OLAP on it and creating tables of years, months, days, and using an intersect table.

Anybody?