In a simple data model, a Contact works for one Account. An Opportunity is linked to a single Account. These are one-to-many relationships. But real-world business isn't always so tidy. What if a consultant works on projects for multiple clients, and each project involves several consultants? This is a many-to-many relationship, a scenario that requires a special architectural pattern.

Enter the junction object an elegant solution to a common problem. A junction object is a custom object that sits between two other objects, breaking down a complex many-to-many relationship into two simple one-to-many relationships. It holds foreign keys to both parent records.

For our consultant example, we'd create a "Project Assignment" junction object. It would have a Master-Detail relationship to the "Consultant" object and another Master-Detail relationship to the "Project" object. This way, a single consultant record can be linked to many project assignments, and a single project can be linked to many project assignments. The junction object itself can also hold valuable data, such as the consultant's billable hours or their specific role on that particular project.

Standard and custom objects are perfect for most business data, but what happens when you need to store billions of records? Think sensor data from IoT devices, web page clickstreams, or detailed audit logs stretching back years. Loading this volume of data into standard objects can lead to slow reports, sluggish queries, and significant storage costs.

This is the problem Salesforce Big Objects were designed to solve. They are a scalable and performant data storage solution for massive datasets on the Salesforce platform. While they look and feel like custom objects, they are powered by a different, big-data-based infrastructure underneath, using databases like Apache HBase.

You can interact with data in Big Objects using standard SOQL or, for massive asynchronous queries, Async SOQL. They are ideal for archiving historical data or for capturing transient event data that you need for analysis but not for day-to-day transactional use. The key trade-off is that Big Objects don't support the full feature set of standard objects, such as triggers, flows, or a standard user interface.

Choosing a Big Object is an architectural trade-off. You gain massive scale and cost-effective storage but give up some of the real-time automation and standard features you're used to. It's about using the right tool for the right job.

Often, the data you need doesn't live inside Salesforce. It might be in an external ERP, a proprietary database, or a data warehouse. The traditional approach is to copy this data into Salesforce using ETL tools. This creates data duplication, potential sync issues, and consumes valuable storage.

A more modern architectural pattern is to access the data in place, in real time. This is the purpose of External Objects and Salesforce Connect It allows you to create objects in Salesforce that map directly to data tables in an external system. Users can view, search, and report on this external data as if it were stored directly in Salesforce, without any data being copied.

To link this external data to your internal Salesforce records, you use special relationship fields. An External Lookup creates a parent-child relationship between a standard Salesforce object and an external object. An Indirect Lookup is more powerful; it links a Salesforce object to an external object using a value from an External ID field, effectively allowing you to join Salesforce data with external data on a common identifier.

Using an External ID is crucial for maintaining a link to a system of record. By marking a field as an External ID, you're telling Salesforce that this field contains a unique identifier from another system. This allows you to use upsert operations, which either insert a new record or update an existing one based on this ID, preventing duplicate records and ensuring data synchronisation is robust.

As data volume grows, poor architectural choices can lead to severe performance degradation. One of the most common problems at scale is data skew This occurs when a single parent record is associated with an enormous number of child records (typically more than 10,000).

For example, imagine a single "General Company" Account that owns millions of Contacts who haven't been assigned to a specific regional office yet. Any operation involving this one Account, like changing its owner or running a report on its contacts, will be incredibly slow because Salesforce has to lock the parent record to ensure data integrity. This lock can cause system-wide slowdowns.

Ownership skew is a related problem, where a single user owns a vast number of records of a single object. This can cause issues with role hierarchy calculations and sharing updates.

As an architect, your job is to anticipate and prevent skew. This might involve creating a more balanced account hierarchy, using a round-robin assignment logic to distribute record ownership, or archiving old child records to keep the parent-child ratio in check. Proactive design is the only way to ensure high performance in a large-scale Salesforce implementation.

These advanced data structures form the backbone of any large, high-performance Salesforce org. Understanding when and why to use each is a key skill for any solution architect.