16.10 - Translating Entities and Relationships Into Tables - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Definition of a Prime Table

A prime table is a table that has a single column primary key.

Prime tables always model entities and all entities are modeled by prime tables.

Ensure that all prime tables have been defined prior to defining any associative tables (see Definition of an Associative Table below).

The following table, Table_A, is prime because it has a simple primary key (see the definition for “Simple key” under Definitions):

Table_A
A_Key
PK
A1
A2
A3

Definition of a Non-Prime Table

A non-prime table is a table that has a composite primary key.

The following table, Table_B, is non-prime because it has a composite primary key (see the definition for “Composite key” under Definitions):

Table_B
B_Key_1 B_Key_2
PK
B1_1 B2_3
B1_2 B2_2
B1_3 B2_1

Definition of an Associative Table

An associative table is a non-prime table whose primary key columns are all foreign keys.

Because associative tables model pure relationships rather than entities, the rows of an associative table do not represent entities. Instead, they describe the relationships among the entities the table represents.

Always define all your prime tables before defining any associative tables.

The following associative table, table_a-b, associates prime table entity table_a with prime table entity table_b:

table_a-b
a_Key b_Key
PK
FK FK
A1 B1
A2 B5
A3 B2

Guideline for Naming Associative Tables

Use the following general form to name associative tables.

   prime_table_name_A-prime_table_name_B

This convention helps to keep the alphabetic and logical sequences of tables synchronized, making it easier to locate the prime tables for the foreign keys that make up its composite primary key.