5

I am designing a table in Teradata with about 30 columns. These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc. It is bad design to store the actual string values in the table since this would be an attrocious repeat of data. Instead, what I want to do is create a primitive lookup table. This table would hold Daily, Monthly, Weekly and would use Teradata's identity column to derive the primary key. This primary key would then be stored in the table I am creating as foreign keys.

The table would be designed like this:

ID  Type         Value
--- ------------ ------------
1   Interval     Daily
2   Interval     Monthly
3   Interval     Weekly
4   TimeFrame    24x7
5   TimeFrame    8x5

This would work fine for my application since all I need to know is the primitive key value as I populate my web form's dropdown lists. However, other applications we use will need to either run reports or receive this data through feeds. Therefore, a view will need to be created that joins this table out to the primitives table so that it can actually return Daily, Monthly, and Weekly.

My concern is performance.

I've never created a table with such a large amount of foreign key fields and am fairly new to Teradata. Before I go on the long road of figuring this all out the hard way, I'd like any advice I can get on the best way to achieve my goal.

Nick Chammas
  • 14,810
  • 17
  • 76
  • 124
oscilatingcretin
  • 209
  • 1
  • 2
  • 8

2 Answers2

4

Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.

I think that Teradata will be able to produce this view efficiently. From what you say, it appears to do little more than join some very small dimension tables against a fact table. The join operations will be relatively efficient. Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.

Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.

Beyond that, all I can suggest is that you suck it and see. If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.

ConcernedOfTunbridgeWells
  • 17,081
  • 2
  • 59
  • 71
-1

You can consider using a "smart key" instead of a meaningless sequential value, which you would then be able to use without necessarily joining with this lookup table:

ID  Type         Value
--- ------------ ------------
1   Interval     Daily
7   Interval     Weekly
30  Interval     Monthly
85  TimeFrame    8x5
247 TimeFrame    24x7

You could then have queries on the fact/main table filtering by the lookup (schedule?) column, without the need for JOINs:

SELECT ...
  FROM MainTable
 WHERE schedule = 30

This way, you can have your dropdown being populated with values from the lookup table, and have efficient queries on the fact/main table.

gonsalu
  • 928
  • 8
  • 17