Data Modelling Tips for Salesforce

What is a Data Model?

  • a way to model what databases tables look like in a way that makes sense
  • made up of data entities – can be objects or concepts? to track data
  • types of data model
    • conceptual – high-level business structures and concepts
    • logical – establish entities, their attributes and relationships
    • physical – internal schema database design

What are objects?

In Salesforce we think database tables as objects and rows are records – an abstraction.

Objects in Salesforce – containers for information which builds the user interface as well

  • Standard objects – objects included in Saleforce.
    • Business Objects
      • Account
      • Contact
      • Lead
      • Opportunities
  • Custom Objects – specific objects created for your company or industry
  • External Objects
  • Platform Events – Kafka abstraction
  • Big Objects

Custom Fields – are columns or attributes abstraction

  • each field has it’s own data type
    • checkbox
    • textfield
    • date or datetime
    • formula – automatically calculated based on a formula written
    • etc.

Creating a Record is same as adding a new row

What are object relationships?

A special field type that connects two objects together – foreign key and primary key abstraction. With different types of relationships, Salesforce determines how the data deletion, sharing and required fields on a page layout will be implemented

  • Master-detail
    • closely links objects together – master records controls certain behaviour of the detail and subdetail record
    • deleting detail keeps the master intact
    • deleting the master deletes the detail(child)
    • by default can’t be reparented. unless special permission is given on attribute definition – ‘Allow reparenting’
    • Owner field on detail/subdetail is the master owner.
    • detail and subdetail inherits security from master
    • each custom object can have up to 2 master-detail relationships and up to 25 total relationships
    • broken permissions exist if child has permission that parent should have
      • Salesforce updates the parent entity on first save for the profile.(master gets the permission)
  • Many-to-Many
    • A junction object
    • each record of one object to be linked to multiple records from another object
  • Lookup Relationships
    • can be one-to-one
    • one-to-many
    • link to same object except user object
    • similar to master detail except no sharing or rollup
    • Limitations: can’t lookup to campaign member object
    • Lookup setup options:
      • make lookup field required
      • clear value of this field
      • don’t allow deletion of the lookup record that’s part of the lookup relationship
      • delete this record also
        • this can result in cascade-delete bypassing security and sharing settings
        • a user can delete the target record and cascade delete records related to it even when though he has no access to the records.
        • contact SF to enable cascade-delete
      • parent record deletion on lookup relationship does not track field history tracking
      • linked objects of more than 100k cannot be deleted, first delete the appropriate child records
  • External Lookup
    • links a child standard, custom or external object to a parent external object
    • external id field on the parent external object is matched with the values of the child external lookup relationship field
  • Indirect Lookup
    • links a child external object to standard or custom objects
    • the parent object custom unique external id field should match the child’s indirect lookup relationship field.
  • Hierarchical
    • special lookup relationship available for only the user object.

Considerations:

  • limitation: custom can have up to two-master detail relationships and many lookups
  • converting relationships
    • master-detail to lookup ?
      • no roll-up summary fields
      • child changes to OWD changes to public read/write
    • lookup to master
      • all lookup fields contain a value
      • OWD changes to controlled by parent
  • self relationships
    • single record can’t be linked to itself
    • indirectly relate to itself
    • can’t create many-to-many self-relationship
  • icons
    • based on tabs on
  • master-detail relationships
    • “Customize Application” user permission to create multilevel master-detail relationships?
    • records by default can’t be reparented. Only Admin can.
    • 3 custom detail levels?
    • standard objects can’t be on the detail side of a custom object master-detail
    • multilevel master-detail relationships do not support division transfer
    • you can’t create master-detail relationship if object already contains data
      • you can do a lookup relationship then convert to master-detail though
    • roll-up works on the detail but not on subdetail
      • create. a rollup on the subdetail
    • junction objects cannot be master
    • deleting sequence has an impact
      • deleting detail and later delete master, you cannot undelete detail
    • Metadata API deployment that includes master-detail deletes all detail records in the bin for the ff:
      • soft delete detail before deploy, then bin will be emptied
      • convert lookup to master-detail, detail must reference a master or soft delete, after deploy bin is emptied
    • Don’t exceed 10k records on master-detail relationship
  • many-to-many relationship
    • junction objects are deleted when either associated master record is deleted
    • if both master are deleted, the junction record can’t be restored
    • OWD and Object permission should match
      • read/write master owd, user must have read/write object permssion on both objects
    • user can’t delete a master if there are 200 junction object records and if junction has a rollup-up summary to other parent
    • first master-detail becomes the primary relationship
      • this affects the look and feel, record ownership inherit from primary
      • division? inherits from primary
    • second primary can be primary if the first primary is deleted/converted to lookup
  • relationship to external objects
    • only support lookup, external lookup and indirect lookup relationship
    • relationships that involve external objects allow users to create child records? however relationship field on each new child isn’t automatically populated with the parent
    • syncing doesn’t create relationship fields on external objects, however you can change the field type of a sync type to lookup, external or indirect lookup
      • change text field that identifies the foreign key which is the primary key
      • relationship field is a type of custom field. this field can be overwritten when you sync external objects – see considerations
      • cascade-delete is not available for external object
      • Salesforce classic
        • indirect lookup relationship fields dont display the expected name of records (eg. Goldman and Sachs, instead it shows the value of the target field external id.
        • same with external lookup. display shows value of the external id in list views.
          • in detail page, it displays the name as expected. but basing on previously retrieving parent record.
      • lookup search isn’t available. enter the external id manually.
      • external lookup and indirect lookup relationships appear as links to the parent record. If there is no access to the parent record then it appears as a plain text instead of a link.
      • lookup filters not available
      • indirect lookup relationships can only be created on external objects
      • only objects that have external id and unique are available as parent objects in indirect lookup.
      • on case-senstive scenario make sure it applies to the parent object field definition too
    • impact of relationships on reports
      • lookup relationships allow data from two related objects to be joined in one report
      • master-detail relationships allow data from three objects to be joined in one report.(master, detail and another lookup)
      • many-to-many provide two standard report types
        • primary master with junction object and secondary master
        • secondary master with junction object and primary master
        • the order of the master objects is important
          • list determines the scope of records that can be displayed

Best Practices

  • Meaningful names for objects and fields, unique names improves clarity
  • Help out your users – include descriptions for your custom objects and fields
  • Require fields when necessary – to keep data clean, you might enforce required fields
  • Dont’ exceed 10k child records for master-detail relationships
lopau

Leave a Reply

Your email address will not be published. Required fields are marked *