Archives of the TeradataForum

Message Posted: Mon, 07 Jul 2003 @ 16:18:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have 2 tables in third normal form: Base and Line. The relationship is one-to-many (one Base to 1 or more lines). The Base table represents claims, whereas Line table represents particular line items for the claims. Both tables have a set of fields called "service begin date" and "service end date". So to find out when a claim was rendered, you can look at the Base service begin/end dates, and to find out when particular line items were rendered you can look at the begin/end dates on the line.

For most of my user's queries, though, they really want to use an amalgam of the Base/Line service dates. For example, when they query the Line table they want the service dates to be the Base dates for some types of claims, and for other types they want the Line dates. So basically for claim types A through G they want to see Base dates, and for types H through M they want to see Line dates (when they query the Line table). Easy enough.

Here is my dilemma: I would like my Bi/Query model for the Line table to show the original Line service dates as well as these "calculated" ones (per above). The question is how to implement the calculated ones.

(1) Creating a view that joins Base and Line is not a good option. It would require a join between the two largest tables in the database everytime user wanted to access the Line table; moreover, if the user wanted to group by these caluclated dates (seeing all possible values), they would blow spool as the two tables would have to be joined before any aggregation was performed (still on V2R4 here). I'm trying to avoid unnecessary joins. If the user isn't even querying on the service dates, then why go through all the work of joining to Base? Also, having a BI/Query model whose "Line" table is actually a view of both Line and Base is ugly in itself.

(2) I can't see how join indexes do anything for me. At best I can define an index to cover some queries, but for the rest of 'em a join would be required between Line and the join index--which is pretty close in cost to joining Base to Line.

(3) Denormalizing the Line table to contain these two fields is on the one hand efficient but on the other ugly--it violates third normal form.

Relevant, viable advice is appreciated.

	<Prev	Next>		<<First	<Prev	Next>	Last>>