This requires a Junction Object — a custom object with two Master-Detail relationships, one to Vendor and one to Product — where each record in the junction represents exactly one Vendor-Product pairing, and fields specific to that pairing, like the supply price, live directly on the junction object itself. A simple Lookup field cannot handle this because a Lookup field on either Vendor or Product can only reference ONE record on the other side per field — it has no way to represent that a single Vendor supplies multiple different Products, each potentially at a different price, without either duplicating Vendor records per Product or cramming multiple Product references into one field, neither of which properly represents genuine many-to-many data or supports clean reporting. The Junction Object pattern is Salesforce's standard, correct solution specifically because it creates one discrete record per relationship pairing, with room for pairing-specific fields like price.

A single custom object can have a maximum of two Master-Detail relationships. This limit matters directly for Junction Object design because a standard Junction Object needs exactly two Master-Detail relationships — one to each side of the many-to-many pair — which fits perfectly within this limit, but leaves no room for a third. If a design scenario seems to call for a genuine three-way many-to-many relationship, a single Junction Object cannot express it directly, since it cannot be Master-Detail child to three different parent objects simultaneously; such a scenario typically requires restructuring into multiple two-way junction objects, or using a combination of one Master-Detail plus Lookup relationships depending on which side genuinely needs cascading delete and Roll-Up Summary behavior versus which side can tolerate a looser connection.

An External ID field holds a unique identifier that originates from a system OUTSIDE Salesforce, giving both Salesforce and the external system a shared reference point that neither system's own internal record ID can provide on its own, since the external system has no knowledge of Salesforce's internal Record IDs and vice versa. This becomes essential for Data Loader's UPSERT operation specifically, because UPSERT needs a reliable way to determine whether an incoming row of data should UPDATE an existing Salesforce record or INSERT a brand new one — it does this by matching the incoming external identifier against the External ID field on existing records, updating on a match and inserting when no match is found. Without a properly configured, Unique External ID field, achieving this same reliable match-or-create behavior would require significantly more manual logic and would be far more prone to creating duplicate records during repeated integration syncs.

The likely underlying cause is that the custom picklist field is not indexed, meaning Salesforce must scan a much larger portion of the millions of existing records to satisfy the filter rather than using an efficient indexed lookup, which becomes a significant performance factor specifically at Large Data Volume scale, even though the same query would feel instantaneous on a small dataset. The realistic options are: first, if there is a legitimate business reason to make the field Unique or use it as an External ID, doing so would grant automatic indexing as a side effect, though this is not always appropriate depending on the field's actual purpose; second, and more commonly, the Admin can open a Salesforce Support case specifically requesting a Custom Index be added to that field, since arbitrary custom field indexing generally is not self-service; and third, reviewing whether the report or query design itself could be adjusted to filter on an already-indexed field, such as a Lookup relationship or CreatedDate, as a primary filter alongside the picklist as a secondary condition.

Storing a list of related values as a comma-separated Text field breaks several things a proper relationship structure would otherwise provide. Reporting integrity suffers significantly, since Salesforce Reports cannot cleanly filter, group, or aggregate on individual values buried inside one larger text string, making it effectively impossible to answer a question like "which Vendors supply Product X" through standard reporting tools. Data integrity also suffers, since free-text entry is prone to inconsistent formatting, typos, and duplicate near-matches like "Silicone Sheet" versus "silicone sheet, " with no validation preventing these inconsistencies. This pattern is generally considered a clear design smell signaling that a proper Lookup, Master-Detail, or Junction Object relationship should have been used instead, since Salesforce's relational structure exists specifically to represent this kind of connected data reliably and reportably.

The design starts with a Course custom object representing the training course itself, which needs an External ID field, such as an LMS Course ID, to enable reliable syncing of completion data with the external Learning Management System. Since each Course has multiple dated Sessions, and a Session cannot meaningfully exist without its parent Course, Course Session should be Master-Detail to Course, with a Date field capturing when that specific session occurs. Because attendance needs to be tracked per session AND an employee can attend multiple sessions across multiple courses, this is a genuine many-to-many relationship between Course Session and the person attending, requiring a Junction Object, Attendance, with a Master-Detail relationship to Course Session and a relationship to Contact or User representing the attendee, with each Attendance record representing one specific person's presence at one specific session. This complete structure — Course, Course Session as its Master-Detail child, and Attendance as the Junction Object connecting sessions to attendees — correctly represents every stated requirement using the relationship patterns covered throughout this module.