Relationship Tables

In our system, we handle two types of relationships: one-sided and two-sided. One-sided relationships involve a table that categorizes connections between records. Two-sided relationships are represented by 'system_relationship' and 'data_relationship' tables, storing system operation data and relationships between data, respectively.

As part of system initialization, we ensure accurate DataSetNumber defaults, crucial for system capabilities when paired with the RCFT abstraction. A two-sided relationship comprises pairs of DataSetNumbers and RowIds, suffixed with '_Parent_Implied' and '_Child_Implied.' Tables have an 'intSequence,' and subjects have their corresponding RCFT.

An important one-sided relationship is the ATTRIBUTE named Reference. Each Reference table, tied to a specific DOMAIN, connects to its parent using a foreign key and includes a pair of DataSetNumber_Implied and RowId_Implied.

Distinguishing between two-sided and one-sided relationships, the former stores frequently changing data, while the latter is for infrequent or static data. For example, a Definition DOMAIN, which dictates system operations, utilizes the Reference ATTRIBUTE for relationships that may never change. On the other hand, data_relationship stores dynamic relationships, such as the Company to Person paradigm (Group DOMAIN to Person DOMAIN).

Making this distinction optimizes data insertion, reducing the data footprint by partitioning infrequent changes. The benefit lies in improved performance for SELECTs and lookups compared to functions and comparisons.

The approach is particularly advantageous for recursive CTEs. When the underlying data changes less frequently than CTEs run, we avoid slowing down the system and gain valuable relationship insights. Storing various relationships in a single table (e.g., Group to Person, Person to Product) automatically provides related connections like Group to Transaction.

A crucial system insight is that SELECTs and lookups outperform functions and comparisons, ensuring optimal system performance. This principle underscores our system's efficiency and opens up exciting coding paradigms.