Q: end dated data subsets

I have a task that is undoubtedly common, simple, but not trivial.

I have a number of tables with minor variations, but basically what happens is this. Each row has a start date and an end date. The rows fall into sets, within a set of rows there can be at most one row that does not have an end date (i.e. "active"). An example is below, to clarify this.

I want to accomplish two things.

1. I want to define a constraint that enforces the rule of one-or-zero "active" rows per set. How to do this is not obvious to me at all.
2. I want to write a trigger that forces older rows to be automatically end dated during various changes. This is "easy", lots of examples exist, but the code is not dead simple whereas I suspect some subtle but super simple technique exists, and I would like to find it.

An example of two "sets". Each set is identified by the id. The PK is the id and the subid. set 1 has an active row and set 2 doesn't.

        id subid start_date end_date

	1	1	1-jan-2000	31-dec-2000
	1	2	1-jan-2001	31-dec-2001
	1	3	1-jan-2002
	2	1	1-jan-2000	31-dec-2000
	2	2	1-jan-2001	31-dec-2001
	2	3	1-jan-2002	31-dec-2002

So, I'm looking for suggestions or pointers on how to define a constraint to enforce the end_date rule (0 or 1 row without an end date per set). Examples of variations on that theme would be just as welcome. I don't think I care whether the dates overlap, or any other complications, just the basic idea of 0-or-1 per set.

If I insert a row with no end date, or update a row so it has no end date, then I would like the trigger to update the other rows in the set to have an end date. The "text book" way to do this is to save the ids of all the modified rows during the row level trigger, and then apply logic during the table level after trigger. Like I said though, the data looks simple enough that I suspect some dead simple code would work if applied with due intelliegence to its design.

For feedback, thanks. Malcolm. Received on Thu Nov 22 2007 - 00:30:10 CST