Data Modeling and Database Design practice questions

Lookup relationship

Why it fails: A standard lookup allows only one-to-many associations. If an activity is linked to a contact via a single lookup field, it cannot be linked to a second contact without creating additional, redundant lookup fields, which is inefficient and does not scale for dynamic association requirements.

Master-Detail relationship

Why it fails: Master-Detail is a one-to-many relationship. While it allows for parent-child hierarchies, it cannot resolve a many-to-many requirement on its own. Using a master-detail relationship would restrict the activity to exactly one contact, failing to meet the business need for multi-contact association.

Text field with IDs

Why it fails: Storing multiple IDs in a text field is a poor data modeling practice. It prevents the use of standard reporting features, prevents referential integrity, and makes queries significantly harder to write. It does not allow for efficient joins or lookups, making it an incorrect architectural choice.

Excessive use of indices

Why it fails: Excessive indexing generally improves query performance and does not cause 'Too many SOQL queries' errors. While too many indices can slow down DML operations (inserts/updates), they are not the primary cause of hitting SOQL governor limits during data processing tasks.

Too many custom objects

Why it fails: The number of custom objects does not correlate with hitting SOQL query limits. Even with a large schema, if the code is written efficiently using bulkification patterns, the limits will not be reached. The error is a reflection of code logic, not the schema complexity.

Lack of Validation Rules

Why it fails: Validation rules prevent invalid data entry and do not impact the number of SOQL queries executed. They operate on a single record basis during the save pipeline and are unrelated to the governor limits associated with bulk apex processing or query execution counts.

They can reference cross-object formulas

Why it fails: Rollup summaries cannot reference formulas that contain cross-object references or functions that derive values from other objects. This limitation exists because the rollup engine requires static, local values to maintain high performance during calculations, and cross-object references would introduce uncontrolled complexity and recursive dependency risks.

They can be used on Lookup relationships

Why it fails: Rollup summaries are not natively supported on lookup relationships. Because lookups do not guarantee the child-parent lifecycle, the system cannot ensure that the aggregate is always current, which is why architects must use other tools like Flow or trigger-based rollups for lookup-based objects.

Schema Builder

Why it fails: Schema Builder is excellent for viewing, but it lacks the capability to store architectural notes, detailed documentation, or version control for the data model. It is a live view, which is not suitable as a static architectural document that needs to be reviewed and approved by stakeholders.

Excel Spreadsheets

Why it fails: Excel is not suitable for visualizing relationships. While it can list fields, it fails to show the relational structure, cardinality, or the 'big picture' of the data model. It makes maintenance difficult and is prone to errors, as updates to the model aren't automatically reflected in the document.

Salesforce Setup Menu

Why it fails: The Setup menu is a configuration interface, not a documentation tool. Browsing the setup menu to understand the model is inefficient and does not provide an architectural overview or a clear way to share the model design with project stakeholders for review and planning purposes.

Storage limits

Why it fails: Storage limits are based on row counts, not the number of columns (fields). While there is a limit on the number of custom fields per object, this is a metadata limit rather than a data storage capacity limit. Adding fields does not consume the organization's data storage space.

Security vulnerabilities

Why it fails: The width of a schema does not inherently introduce security vulnerabilities. Security is managed via profiles, permission sets, and field-level security. While managing security for 500 fields is complex, the sheer number of fields itself is not a vulnerability, just an administrative and architectural burden.

API call limits

Why it fails: API call limits are determined by the number of requests made to the platform, not by the width of the tables being accessed. Querying a table with 10 fields vs 500 fields does not change the API consumption count; however, the data payload size will increase, which is a different concern.

Minimize the number of indexes on the Account object.

Why it fails: Indexes are essential for query performance in large datasets. Minimizing them would likely lead to full table scans, drastically increasing execution times. Instead, architects should selectively index fields that are frequently used in query filters, ensuring high performance without hitting governor limits associated with excessive index updates.

Use Master-Detail relationships for all custom objects to increase speed.

Why it fails: Master-detail relationships create implicit database locks and can contribute to data skew, especially if many children are reparented. While they offer benefits like cascading deletes and roll-up summaries, they are not a performance panacea and can actually exacerbate record-locking issues in highly transactional environments if not used cautiously.

Store all historical data in the core Salesforce database.

Why it fails: Storing excessive historical data in the core database impacts query performance and storage limits. Best practice dictates archiving old data to an external system or Big Objects. This keeps the active data model lean, ensures faster query responses, and avoids the performance penalties associated with scanning massive, stagnant datasets.

The query is hitting the 50,000 record limit.

Why it fails: The record limit refers to the number of records returned in the result set, not the efficiency of the query execution plan. A non-selective query error happens before the result set is even compiled, because the database optimizer cannot find an efficient path to retrieve the requested records.

The Account object has too many total fields.

Why it fails: While having too many fields can impact object performance, it is not the cause of a non-selective query error. That error is specifically related to how the database engine evaluates filter criteria during data retrieval, which depends on indexes and the distribution of data, not the total field count.

The SOQL query is using a wildcard in the WHERE clause.

Why it fails: Wildcards in SOQL queries (like LIKE 'Value%') can impact performance, but the exhibit shows a date-based range filter, not a string pattern match. The error described is clearly related to the selectivity of the filter, which is a structural data modeling issue rather than a syntax-related wildcard issue.

The field will not be searchable in global search.

Why it fails: External ID fields are indexed automatically, which actually makes them searchable. Marking an external ID as unique has no impact on global search visibility. The primary issue with non-unique external IDs is the failure of upsert logic during data integrations, not the searchability of the records themselves.

Reporting will be slower due to index fragmentation.

Why it fails: Index fragmentation is a backend database concern that is not directly tied to the uniqueness constraint of a field. The primary impact of non-unique external IDs is functional—it breaks the 'upsert' capability. Reporting performance is impacted by query filters, not the uniqueness of an identifier field.

The field cannot be used in cross-object formulas.

Why it fails: Uniqueness is not a requirement for a field to be used in cross-object formulas. A field can be used in formulas regardless of whether it is indexed, external, or unique. The risk of non-unique external IDs is entirely related to the integrity of the data import and synchronization processes.

Delete all child records and re-insert them after the conversion.

Why it fails: Deleting records is an unnecessary and dangerous step that leads to data loss. The conversion process is designed to handle existing records, provided they are already linked to a parent. Deletion is not a requirement and should be avoided to preserve related historical data and audit trails.

Disable all validation rules on the child object.

Why it fails: Validation rules do not interfere with the metadata conversion process of changing a relationship type. They are meant to validate data input, not system schema changes. Disabling them is unnecessary and would not facilitate the migration, as the platform handles the schema modification independently of record-level business logic.

Field-Level Security (FLS).

Why it fails: Field-Level Security controls access to individual fields, not the records themselves. Even if all fields were hidden via FLS, the records would still be visible in search results and reports. FLS is a complementary layer, not a replacement for object-level access control, and it cannot hide the entire record.

Permission Sets.

Why it fails: Permission Sets manage functional access like 'View All' or 'Modify All' for an object. They are not designed to filter record visibility based on data ownership or specific criteria. Using permission sets to manage record access would lead to administrative bloat and is not the intended use case for object access.

Validation Rules.

Why it fails: Validation rules ensure the quality of the data entered into the system. They do not have the capability to hide or display records based on user context. Using validation rules to secure data would be ineffective, as the record would still be fully visible to unauthorized users in reports and views.

Allow all integrated systems to modify the customer records directly.

Why it fails: Decentralized modification creates data conflicts, duplication, and integrity issues. Without a central authority for customer data, different systems will maintain conflicting versions of the truth, leading to inconsistent reporting and poor customer experiences. MDM requires a controlled, centralized flow of information to maintain data quality and consistency.

Sync customer data nightly using a flat file CSV dump.

Why it fails: Nightly CSV syncing is outdated, prone to latency, and fails to handle real-time data needs. It leads to outdated information and high risk of data collision during processing. MDM strategies today prioritize near-real-time updates and API-based integration to ensure the master record is always as accurate and up-to-date as possible.

Use the Salesforce standard Account object without customization.

Why it fails: Standard objects rarely satisfy complex MDM requirements out of the box. Custom fields, validation logic, and specific indexing are usually required to ensure the record is truly 'mastered'. Relying solely on standard functionality often leads to data quality gaps and an inability to track the lifecycle of the golden record.

Configure standard Field-Level Security to make all fields read-only for all profiles.

Why it fails: Standard field-level security configurations restrict regular business users effectively but fail to prevent system administrators from modifying the data. Administrators retain ultimate read and write access, bypassing standard profile restrictions entirely during data correction tasks.

Write a before-update Apex trigger that throws an error whenever a legacy order record is modified.

Why it fails: Apex triggers executing before update can successfully prevent standard users from modifying records, but system administrators can still bypass triggers using specific API settings or by temporarily disabling automation through custom settings.

Deploy a validation rule checking a custom 'Is_Locked' checkbox field set to true upon record creation.

Why it fails: Validation rules evaluate upon record save operations to enforce business logic, yet system administrators can easily bypass validation rules unless complex profile-checking exemptions are absent, failing to guarantee true absolute immutability.

Create a master-detail relationship from the Account_Review object to both the Account and User objects.

Why it fails: Salesforce allows a maximum of two master-detail relationships per custom object, but establishing master-detail links to User objects is unsupported because User is a standard setup object that cannot act as a master in a master-detail configuration.

Rely entirely on implicit sharing by making Account_Review a child in a master-detail relationship with Account.

Why it fails: Master-detail relationships automatically inherit sharing from the parent object via implicit child sharing. However, because the review team members might not be the direct owners or account team members, this approach exposes reviews too broadly.

Configure criteria-based sharing rules targeting the Account_Review object based on custom user lookup fields.

Why it fails: Criteria-based sharing rules evaluate record fields against explicit filter criteria. They cannot evaluate across junction relationships dynamically to evaluate User records referenced in complex multi-to-many associations without hitting rule limits.

Implement a 5-level deep Master-Detail relationship hierarchy.

Why it fails: Salesforce supports a maximum of three levels of Master-Detail relationships. Exceeding this limit prevents the creation of the schema entirely. Even if it were supported, deep hierarchies significantly increase the complexity of record locking and cascading security evaluations, leading to severe performance degradation in high-volume environments.

Always use SOQL joins to fetch data regardless of depth.

Why it fails: SOQL has strict limits on the number of joins and the depth of relationship navigation. Using excessive joins in a single query will lead to 'Too many query levels' errors and potentially hit governor limits for heap size or execution time, especially when working with millions of records.

Increase the number of lookup relationships on the object.

Why it fails: Adding more lookup relationships increases the complexity of the data model but does not solve the underlying issue of query depth. In fact, excessive lookups can complicate index optimization and increase the likelihood of hitting 'Query Plan' issues, as the optimizer has more paths to evaluate during execution.

Remove the CreatedDate filter from the query.

Why it fails: Removing filters does not improve performance; it often makes it worse because the database has to process more records. In fact, filtering on a date field can be highly efficient if that field is indexed, which 'CreatedDate' is by default in most Salesforce environments.

Change the query to use a SOQL 'FOR VIEW' clause.

Why it fails: The 'FOR VIEW' clause is used to update the 'LastViewedDate' of records returned in the query. It does not provide any performance optimization for the retrieval of records. In fact, it adds overhead by performing an extra write operation for every record returned by the query.

Replace the OR operator with an AND operator.

Why it fails: Changing the logical operator changes the business requirement, not the performance characteristics of the query. While an AND condition might be more selective, you cannot change the business logic just to satisfy a performance requirement without verifying the impact on the application's functionality.

Delete all records older than one year immediately.

Why it fails: Unilaterally deleting records can violate data retention laws and business requirements. A proper lifecycle policy requires business input to determine which records are truly obsolete, rather than relying on a simple time-based rule that could destroy valuable historical data needed for compliance or analytical trend analysis.

Move all data to Salesforce Files to save space.

Why it fails: Salesforce Files are a form of storage that also contributes to the organization's overall limits. They are intended for documents, not for record-level data. Moving relational data into files would make it impossible to query or report on, destroying the utility of the data for business applications.

Excessive use of custom indexes on the Opportunity object.

Why it fails: Custom indexes do not cause locking errors; they help the database retrieve records faster. The root cause of locking is typically the relationship model and the way concurrent processes access the same parent record and its children, not the presence of indexes on fields.

The use of 'Person Accounts' in the org.

Why it fails: While Person Accounts affect the data model, they do not inherently cause record locking issues for unrelated Opportunity updates. Locking is a function of the record ownership and relationship structure, not the use of Person Account record types within the Account object itself.

The org is exceeding its daily API limit.

Why it fails: API limits restrict the number of calls, not the performance of the database or the occurrence of record locking. If an API call is successful but the database times out due to a lock, it is a data modeling issue, not an API quota issue.

Use a series of custom checkboxes for each stage.

Why it fails: Checkboxes are poor for state management because they allow multiple stages to be selected simultaneously, violating the integrity of a state machine. It is difficult to report on or enforce order with boolean values, leading to data inconsistency and complex validation logic that is hard to maintain.

Use a Multi-select picklist to track historical stages.

Why it fails: Multi-select picklists are notoriously bad for state tracking as they don't provide a history of when or in what order stages were reached. They are also difficult to report on, making them a poor choice for enforcing a strict, sequential process flow within the Salesforce database.

Use the 'Stage' field on the Opportunity object only.

Why it fails: While the Opportunity stage is a state machine, the question asks for a general design pattern for custom objects. Relying on hard-coded standard features is not a repeatable architectural pattern for all objects, and the architect needs to define a general solution for any object needing a state machine.

Use a single 'Global' object to store all legacy data.

Why it fails: Creating a 'Global' object is a major anti-pattern. It leads to a massive, unmanageable object that violates normalization principles and complicates reporting. The data should be mapped to the appropriate Salesforce standard or custom objects to benefit from the platform's native features and security model.

Import all data as text fields to avoid validation errors.

Why it fails: Importing everything as text is a dangerous practice. It bypasses essential data validation, leading to poor data quality, incorrect report results, and broken business logic. The architect should instead ensure that the source data is transformed to match the target schema's requirements before the import occurs.

Add 10 separate address fields directly on the Account object.

Why it fails: Adding multiple address fields directly to the Account object creates a flat schema that is difficult to query and maintain. It consumes significant space, leads to sparse records, and complicates reporting across different address types, ultimately violating standard normalization principles essential for clean Salesforce data architectures.

Use a custom field with a delimited string to store JSON-formatted addresses.

Why it fails: Storing complex data as a delimited string or JSON blob within a single field makes it impossible to perform native Salesforce reporting, filtering, or validation. This approach breaks relational integrity, prevents the use of standard address verification tools, and forces developers to write complex code for simple data access.

Configure a Lookup relationship on the Account pointing to a custom Address object.

Why it fails: Using a Lookup relationship on the parent Account object limits the structure to a one-to-one relationship unless specific complex automation is built. It fails to provide the cascading delete functionality or the ability to use Rollup Summary fields, which are critical for managing child record lifecycle events effectively.