subtables

Subtables are subordinate tables that originate from a larger table in a data model or report. They are used to organize data into smaller, related units while maintaining a connection to the parent dataset. Subtables can be logical, produced by queries that filter or group data, or physical, represented by separate tables that hold related rows in a one-to-many relationship or as partitioned storage within a database.

Logical subtables are commonly created as views or temporary tables that present a subset of rows or columns from the base table. Physical subtables are implemented as separate tables connected by foreign keys, or as partitions that split the base data across storage units while keeping a common schema.

Subtables support drill-down analysis, access control, normalized data organization, and reporting. They allow analysts to focus on a specific department, order type, or time period without duplicating data.

Key concerns include referential integrity between the parent and subtable, update and delete cascades, and performance. Indexes, views, and appropriate partitioning can improve query speed, while ensuring consistency across related data.

An example is a table named Orders with a subtable named OrderItems that stores items for each order, linked by a foreign key. Another example is a report that presents a subtable of customers who placed orders in a given quarter.

Subtables are related to broader concepts such as views, partitions, and relational design patterns for one-to-many relationships.