Multi-Table Lookups and Design Patterns

Multi-Table Lookups — At a Glance

Multi-table lookups occur when a field (or lookup) in a table can reference data in more than one other table. They’re used to model relationships and link data dynamically across multiple sources or entities.

🔄 Common Examples

Use Case	Description
CRM Activities	An `Activity` can be related to `Contact`, `Lead`, `Opportunity`, etc.
Audit/Logs	A `LogEntry` can reference many types of entities (user, system, etc.)
Attachment Tables	A `File` might belong to `Email`, `Note`, `Post`, or a custom record
Permissions	A `RoleAssignment` might point to `User`, `Group`, or `Team`

Design Patterns for Multi-Table Lookups

1. Polymorphic Association Pattern

A single foreign key (e.g., target_id, target_type) refers to multiple tables.
Common in ORMs like Entity Framework or Django with a ContentType field.

CREATE TABLE Comments (
  id INT,
  content TEXT,
  target_id INT,
  target_type VARCHAR(50) -- e.g., 'post', 'photo', etc.
)

Used heavily in Dataverse with fields like regardingobjectid.

2. Join Table with Metadata

Separate join table includes entity type and ID.
Often used when polymorphic constraints are needed in many-to-many scenarios.

CREATE TABLE EntityLink (
  source_table VARCHAR(50),
  source_id INT,
  target_table VARCHAR(50),
  target_id INT
);

3. Interface Table (Base Table Inheritance)

Define a base table with shared fields, and use table-per-type inheritance for specifics.

Table: EntityBase (id, name, created_at)
Table: Account (id FK -> EntityBase.id)
Table: Contact (id FK -> EntityBase.id)

Pattern seen in EF Core and similar systems as TPT (Table-Per-Type).

4. EAV Model (Entity-Attribute-Value)

Useful when referencing custom attributes across entities.
Often used in healthcare, product catalogs, and metadata-driven systems.
Not great for performance, but very flexible.

5. Denormalization (Materialized View Pattern)

Use when read performance trumps storage efficiency.
Combine multi-table lookups into a view or snapshot.

CREATE MATERIALIZED VIEW ActivitySummary AS
SELECT a.id, u.name AS user_name, c.name AS contact_name
FROM Activities a
LEFT JOIN Users u ON a.user_id = u.id
LEFT JOIN Contacts c ON a.contact_id = c.id;

Tools / Platform-Specific Notes

Microsoft Dataverse

Uses Polymorphic Lookups like regardingobjectid.
Type, Id, and sometimes LogicalName are used to resolve the relationship.
In Power Fx / Power Automate, you often filter on .Type and access .Name.

ORMs (e.g., Django, SQLAlchemy)

Use GenericForeignKey or similar patterns to map to multiple tables.
Requires care when querying to avoid performance hits.

Best Practices

Use indexes on ID and type fields in polymorphic tables.
Keep the number of target types minimal if possible.
For complex queries, consider flattened read models or cached denormalized tables.
Avoid using EAV unless absolutely necessary — they’re hard to query and maintain.
In app logic, isolate polymorphic resolution logic (e.g., via a repository or mapper pattern).