| Associating Effective-Dated Tables | KEVIN RESCHENBERG
04-18-2005 |
Murtaza was reviewing my previous posts on effective-dated tables
and wrote to ask about a situation in which two tables with effective
dates have a many-to-many relationship and must be associated using a
third, bridge, table. In that case, does the third table inherit both
effective dates or have its own?
Murtaza is creating a new benefits application containing a Benefit
Program table and a Plan Type table. These have a many-to-many
relationship, so a third table—the Benefit Program Plan Type table—is
needed.
I suggested that we approach the problem by thinking about the logical
purpose of the bridge table and what it tells us. The table indicates
that "this plan type is available as part of this benefit program
as of a particular point in time." The "as of a particular point in time"
applies to this relationship between one program and one plan type.
Therefore, I think the answer is that this table should have its own
(single) effective date field, and it should
be independent of the other two. For example:
Benefit Program Table:
Benefit program = BP1 Effective date = 2005-01-01
Plan Type Table:
Plan type = PT1 Effective date = 2004-07-01
Benefit Program Plan Table:
Program = BP1 Plan type = PT1 Effective date = 2005-02-01
This combination of Program and Plan Type is effective starting February 1, even though the
Program became available on January 1 and the Plan Type was around since last year. It is possible that the Benefit
Program Plan Table effective date could be the same as one of the other effective dates. The only requirement
is that it cannot be earlier than either of the other dates.
In the special case of a parent/child relationship, if a parent table has an effective date in its key, then
any true child table must have that same effective date. JOB and COMPENSATION are like this. If you
are looking at a COMPENSATION row, you can look up the parent JOB row using the exact value of COMPENSATION's
effective date field. The PeopleSoft-delivered benefits tables are similar and form a four-level
parent/child structure of tables (program, plan, option, cost). Each table has an effective date field,
but the values in those fields are all the same for each year's program.
What if you have a parent table containing an effective date key, and a child table that also varies
by its own effective date? In that case, you would need two different effective date fields in the key.
(Murtaza's application could be structured in this way.) Offhand, I can't think of any delivered
tables that are like this—do you know of any?
|
