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).
The following table, Table_A, is prime because it has a simple primary key (see the definition for Simple key in 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 in 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.