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

foreign keyperformanceprimary-keyteradata

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.

Best Answer

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.

Related Solutions

EAV-like table with both primary column pointing to the same foreign column

A simple CHECK constraint works just fine:

$ sqlite
SQLite version 3.8.4.1 2014-03-11 15:27:36
...
sqlite> CREATE TABLE table_B(
   ...>     user1,
   ...>     user2,
   ...>     [other stuff],
   ...>     CHECK (user2 < user1)
   ...> );
sqlite> INSERT INTO table_B VALUES (1, 0);
sqlite> INSERT INTO table_B VALUES (2, 3);
Error: CHECK constraint failed: table_B

(This has nothing to do with EAV.)

Sql-server – How to create a Foreign Key to a Compound Primary Key with a single column

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space:

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100),
    FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY (FieldName, Blah) 
    REFERENCES Lookup (FieldName, Value) ;

Tested at SQL-Fiddle.

The table can then be used as if the FieldName does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2

The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY('This', Blah) 
    REFERENCES Lookup (FieldName, Value) ;

Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.

For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

If you really don't want to use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)

Best Answer

Related Solutions

EAV-like table with both primary column pointing to the same foreign column

Sql-server – How to create a Foreign Key to a Compound Primary Key with a single column

Related Question