How would you select the most recent record? Like this?
select *
from tbl
where StartDate = (select max(StartDate) from tbl)
That's a little cumbersome and seems inefficient though I haven't tested it.
Consider having a boolean isMostRecent column. This column is 0 for all records except the most recent record. In this way, here's how you'd select the most recent record:
select *
from tbl
where isMostRecent = 1
To affect this, you'll need a two step insert procedure (with a transaction):
update tbl set isMostRecent = 0 where isMostRecent = 1
insert into tbl (..., isMostRecent) values (..., 1)
With this approach, you're storing calculated fields. Here's some discussion on the pros and cons of storing calculated fields. With an index on isMostRecent, this could speed up performance. It depends on how you're using this table.
Edit: This doesn't preclude you from having StartDate and EndDate columns. You'll probably still want a StartDate column at least so you can look back in time.
