Search code examples
performanceforeign-keysprimary-keyprimitiveteradata

Teradata: How to design table to be normalized with many foreign key columns?

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.

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.

Edit: I suppose I should add that this lookup table would be a mishmash of unrelated primitives. It would contain group of values relating to time intervals as already mentioned above, but also time frames such as 24x7 and 8x5. 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

Edit Part 2: Added a new tag to get more exposure to this question.

Solution

  • What you've done should be fine. Obviously, you'll need to run the actual queries and collect statistics where appropriate.

    One thing I can recommend is to have an additional row in the lookup table like so:

    ID  Type         Value
--- ------------ ------------
0   Unknown      Unknown

    Then in the main table, instead of having fields as null, you would give them a value of 0. This allows you to use inner joins instead of outer joins, which will help with performance.