Archives of the TeradataForum
Message Posted: Mon, 08 Jan 2001 @ 18:58:48 GMT
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,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|