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.


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.

  • I am thinking I am just going to have to wing it because I don't think anyone else is understanding my question or I am not understanding their answers. – oscilatingcretin Apr 26 '12 at 22:21
  • 1
    What do you not understand? This answer by @COTW is (in short): "(I think that) performance will not be an issue. Having a large fact table with 30 FKs to small lookup-tables and using Joins will be efficient. In all cases, try it out and see how it performs and what the query plan is." – ypercubeᵀᴹ Apr 26 '12 at 22:32
  • COTW's is a good answer. I am referring to the other answer and some of the comments there. I'll just create this huge table tomorrow and blast a bunch of records into it and see how it goes. It will take a few hours, but it'll prob be the best way – oscilatingcretin Apr 26 '12 at 23:11

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:

  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.

  • I am not so sure I understand the smart key concept and how it will keep me from having to join out to the primitives table. Populating dropdowns is easy with select * from primitives where type = 'interval'. The issue is that, when other apps/people go to select from the main table, all they will see are a bunch of meaningless numbers. How can I get a view to show Daily instead of 1 without using a join? – oscilatingcretin Apr 26 '12 at 20:40
  • Retrieve the numbers from the database, and substitute human-readable versions (30 => "Monthly") with application logic. – Jon of All Trades Apr 26 '12 at 21:20
  • Yeah but doing the work in database (Joins) or in the application code is independent on whether you use "smart" keys or non-descriptive random ids. – ypercubeᵀᴹ Apr 26 '12 at 22:08
  • I know how to do it in the app. The app is not the issue. This is outside the app when others will be querying the table with SQL. When they get results back, all they will see are meaningless numbers. Look at these numbers as foreign keys, except they refer to a record in a lookup table. I will need to create a view that returns things like Daily, Monthly, 8x5, etc instead of their key values. The only way I know how to do this is through a join which the answers says I don't need to do. Am I not explaining myself well here? – oscilatingcretin Apr 26 '12 at 22:19
  • Obviously, if the primitives change frequently, you can't get away with anything else other than a JOIN with the lookup table. However, if the primitives don't change that often, or are pretty much static, for reporting purposes, you could use a smart key directly in the queries, as they aren't that meaningless -- that's the idea. – gonsalu Apr 27 '12 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.