Archives of the TeradataForum

Message Posted: Mon, 08 Jan 2001 @ 18:58:48 GMT

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

Hi,

I recently come to Teradata from a heavily DB2 background and would like to raise a philosophical design issue (I've discussed offline with a couple of ye so far - thanks).

WIth DB2 mission critical databases over the years I've always gone with normalised relational designs and only denormalised for exceptional performance improvements. Simplistically, this plays to DB2 index-exploitation strength.

With Teradata the advice we've generally received from various quarters has been to go for fatter, denormalised designs to minimise joins for reasons of performance and ease-of-end-user-sql. Simplistically, this plays to Teradata data-scanning strength.

We're a bank and and are currently modelling how we will hold transactions in the DW of a wide variety of transaction types from a wide variety of source systems. Each transaction will have a small amount of data generic to all transactions and a lot of data unique to that individual transaction type. Classical Super-Type, Sub-Type stuff.

Technical implementation of this will fall somewhere between the following poles:

(a) Very thin Super-Type table (mandatory data for all transactions) with a separate Sub-Type table for each transaction type.

(b) Very thick Single table containing all possible columns - each row containing data for the columns relevent to a particular transaction type with all other columns null.

I can think of various Pro's and Con's of both:

(a: Pro) Pure relational / Very fast access to Super-Type alone

(a: Con) Sql join performance / Sql join complexity / Pre-Load batch split of Super & Sub data / More tables to load

(b: Pro) Sql scan performance / Less sql joining / Easy data model for non-tekky-end-users / Fewer tables to load

(b: Con) Impure relational / sql null handling / Longer data rows to manipulate especially when uncompressed.

I know that it's an impossibly vague question and the answer is undoubtedly 'somewhere between the poles' and 'it depends...' but I'd like to get a feel from which pole you tend to start off and what concepts you tend to apply on the trip!

Thanks a mill,
Michael McMorrow,
AIB Bank, Ireland.

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