Describe All-key Relations in Database Design

An all-key relation is a relation in which every attribute is part of the relation's primary key. In practical database design, this usually appears when a many-to-many relationship is resolved by an associative entity, also called an intersection table, bridge table, or junction table. The table exists to record valid combinations between two parent tables, and it does not need any additional descriptive columns.

All-key relations are important because they show that a table does not always need non-key descriptive data to be valid. Some tables exist only to store relationship facts. For example, a table might record that a product is available in a specific language, that a student is enrolled in a specific course, or that a book belongs to a specific subject category. In each case, the meaningful fact is the pairing itself.

A relation is called an all-key relation when every column in the table participates in the primary key. In the example diagram, the AVAILABLE_IN table contains two columns:

• LangID, which identifies a row in the LANGUAGES table.
• ItemNo, which identifies a row in the ITEM table.

Together, these two columns form the primary key of AVAILABLE_IN. Both columns are also foreign keys. LangID references LANGUAGES, and ItemNo references ITEM. Since there are no additional columns in AVAILABLE_IN, every column is part of the primary key. That makes AVAILABLE_IN an all-key relation.

AVAILABLE_IN (
    LangID,
    ItemNo,
    PRIMARY KEY (LangID, ItemNo),
    FOREIGN KEY (LangID) REFERENCES LANGUAGES(LangID),
    FOREIGN KEY (ItemNo) REFERENCES ITEM(ItemNo)
)

The composite primary key prevents duplicate item-language combinations. For example, the same item should not be recorded as available in the same language more than once. The primary key enforces that rule directly.

All-key relations commonly occur when a database designer resolves a many-to-many relationship. A many-to-many relationship cannot be implemented with a single foreign key in either parent table without creating design problems. If languages were stored directly in the ITEM table, the table might need columns such as Language1, Language2, Language3, and so on. That design would violate the first normal form principle that each field should store one value, and it would create a repeating group.

A better design separates the item facts from the language facts and stores the relationship between them in an associative table. ITEM stores facts about items. LANGUAGES stores facts about languages. AVAILABLE_IN stores facts about which items are available in which languages.

This separation gives each table a clear purpose. ITEM does not need to know how many languages an item supports. LANGUAGES does not need to know how many items use a language. AVAILABLE_IN records the combinations. If an item becomes available in another language, the database inserts another row into AVAILABLE_IN rather than altering the structure of ITEM.

A composite primary key and an all-key relation are related concepts, but they are not identical.

A composite primary key is a primary key made from more than one column. An all-key relation is a relation where every column belongs to the primary key. A table can have a composite primary key without being an all-key relation.

AVAILABLE_IN is both a composite-key table and an all-key relation:

AVAILABLE_IN (LangID, ItemNo)

The primary key has two columns, so it is composite. The table has no other columns, so it is all-key.

Now suppose the table is changed so that it stores an additional relationship attribute:

AVAILABLE_IN (
    LangID,
    ItemNo,
    TranslationStatus
)

In that version, LangID and ItemNo might still form the primary key, but TranslationStatus is not part of the key. The table would still be an associative table, and it might still resolve a many-to-many relationship, but it would no longer be an all-key relation.

An associative entity is a table that represents a relationship between two or more entities. In the ITEM and LANGUAGES example, AVAILABLE_IN is an associative entity. It represents the fact that a particular item is available in a particular language.

A row in AVAILABLE_IN might mean:

LangID = EN
ItemNo = 1001

This row states that item 1001 is available in English. Another row might state:

LangID = ES
ItemNo = 1001

This second row states that the same item is available in Spanish. The pair of values is the fact being stored. There is no need to add another column unless the relationship itself has additional attributes, such as release date, translation status, localization vendor, or availability region.

When the relationship has no additional descriptive data, the associative table often becomes an all-key relation. When the relationship does have descriptive data, the associative table becomes a regular relation with key columns and non-key columns.

The following SQL example shows how the ITEM, LANGUAGES, and AVAILABLE_IN tables could be defined in a modern relational database. The exact data types can vary by database platform, but the primary key and foreign key logic remains the same.

CREATE TABLE Item (
    ItemNo       INTEGER PRIMARY KEY,
    ItemTitle    VARCHAR(100) NOT NULL,
    ItemMusic    VARCHAR(100),
    ItemReader   VARCHAR(100),
    ItemPrice    DECIMAL(10,2),
    RetailPrice  DECIMAL(10,2),
    AgeGroup     VARCHAR(40),
    Language     VARCHAR(40)
);

CREATE TABLE Languages (
    LangID    VARCHAR(10) PRIMARY KEY,
    LangName  VARCHAR(50) NOT NULL
);

CREATE TABLE Available_In (
    LangID  VARCHAR(10) NOT NULL,
    ItemNo  INTEGER NOT NULL,

    PRIMARY KEY (LangID, ItemNo),

    FOREIGN KEY (LangID)
        REFERENCES Languages(LangID),

    FOREIGN KEY (ItemNo)
        REFERENCES Item(ItemNo)
);

The Available_In table has two constraints that matter for this lesson. First, the primary key constraint says that each LangID and ItemNo combination must be unique. Second, the foreign key constraints say that each LangID must already exist in LANGUAGES and each ItemNo must already exist in ITEM.

All-key relations are useful because they express relationship facts with precision. The AVAILABLE_IN table does not duplicate item descriptions or language descriptions. It stores only the valid pairings between items and languages.

This improves data integrity. If a language name changes, the change belongs in the LANGUAGES table. If an item title changes, the change belongs in the ITEM table. The AVAILABLE_IN table does not repeat those descriptive values, so it does not need to be updated when those descriptions change.

This design also supports flexible growth. If a new language is added, a new row can be inserted into LANGUAGES. If an existing item becomes available in that language, a new row can be inserted into AVAILABLE_IN. The table structure does not need to change.

However, it is important not to overstate the performance benefits. All-key relations support logical correctness and normalization, but query performance depends on indexes, table size, database engine behavior, data distribution, and workload. A normalized design is usually easier to reason about, but performance still needs to be evaluated in the target database system.

An all-key relation stops being all-key when a non-key attribute is added. This often happens when the relationship itself becomes more descriptive.

For example, suppose the database must track when an item became available in a language:

Available_In (
    LangID,
    ItemNo,
    AvailableDate
)

If AvailableDate is not part of the primary key, then the table is no longer all-key. It is still a valid associative table, but it now contains a non-key attribute that describes the relationship.

This is not a problem. It simply changes the classification of the table. Database design should follow the meaning of the data. If the relationship has attributes of its own, those attributes belong in the associative table.

All-key relations often appear in normalized schemas because normalization separates independent facts into separate relations. In higher normal forms, such as fourth normal form and fifth normal form, decomposition may produce relations that contain only key attributes. This is especially common when the database must represent independent multi-valued facts.

The important design principle is that each table should represent one kind of fact. ITEM represents item facts. LANGUAGES represents language facts. AVAILABLE_IN represents item-language availability facts. Because that relationship fact needs only the identifiers from the two parent tables, the result is an all-key relation.

An all-key relation is a relation in which every column is part of the primary key. In the ITEM, LANGUAGES, and AVAILABLE_IN example, AVAILABLE_IN is the all-key relation because LangID and ItemNo are both part of the primary key, and there are no other columns.

All-key relations are common in associative entities that resolve many-to-many relationships. They are not design errors. They are useful when the only fact that needs to be stored is the valid combination of parent-table identifiers.

The main design rule is straightforward: if a many-to-many relationship has no descriptive attributes of its own, the associative table may be an all-key relation. If the relationship later needs descriptive attributes, the table can still be valid, but it will no longer be all-key.

[1] all-key relation: A relation in which every field is a member of the relation's primary key.

[2] foreign key: A field or combination of fields used to link tables; a corresponding primary key field occurs in the same database.