Transition from MSSQL to Force.com

I saw an excellent question on the #askForce Twitter feed from @corycowgill today that was “Besides standard Force.com Dev Guide, anyone know good blogs / materials to ramp up a DBA on Force.com?”

I thought, “well heck, I’m an old school Microsoft VB/.NET/MSSQL programmer and I definitely had some ramp-up when transitioning to the Force.com platform.”  I figured I’d have a stab at it – trying to explain what I ran into in hopefully simple english.  =)

Step 1:  Let’s get some common footing.  Name, rank, and serial number soldier!

Let’s tackle the basics first…tables, fields, stored procs, triggers….what are they called in Force.com?

  • Table = Standard and Custom sObject
  • Field = Standard and Custom Field
  • Relationship = Master/Detail and Lookups
  • Programmatibility (stored procs and triggers) = Workflows and APEX (classes and triggers)
  • Index = yes, but not what you’re used to…
  • View = not available in Force.com

Elaborating…

Naming Convention.

  • “Label” vs “API Name”
    • Label is what your users will see that sObject or field as in the interface
    • API Name is what you will reference that sObject or field as in APEX
  • “__c” – this is a suffix you will see appended to the “API Name” of all custom sObjects and fields you create

sObjects.

You have a collection of sObjects called “standard sObjects”.  These are objects that are pre-built into your instance and cannot be deleted.  They are primarily under control by Salesforce (fields can be added as a result of a maintenance release).  You can add your own fields (custom fields) to **MOST** standard sObjects.  The “custom sObject” is a table that you create – fields, security, relationships, etc.  Standard and custom sObjects all live in the same partition – you can access both standard and custom objects from each other.

Examples of standard sObjects = Account, Contact, Event, Task, Opportunity, Campaign, Case.

Examples of custom sObjects = MyObject__c, YourObject__c, YourMom__c.

For sObjects, depending on the Edition you are subscribed to, you can have between 5 and 2,000 custom sObjects.

Fields.  

Just as you have standard and custom sObjects, you have the same with fields.  Standard fields will have names such as “FirstName, LastName, MailingAddress”, where custom fields will have “MyField1__c, MyField2__c, etc”.  Just as with MSSQL, you have the option of defining the data type for a field (I won’t go into all of them, see this link for details).

For fields, depending on the Edition you are subscribed to, you can have between 5 and 800 custom fields per object.

Relationships.

Force.com has two kinds of table relationships:

  • Master-Detail (1:n) – allows a master object to control behaviors of a child object (security model, cascading actions, etc)
  • Lookup (1:n) – links two objects together, but has no bearing on security and the option of cascade actions

You can indeed to many-many (n:n) relationships in Force.com using either of the above relationship types above very similar to how you would use a junction table in MSSQL.

For relationships, you can define up to TWO Master-Detail and FIVE Lookup relationships per sObject.

Programmatibility.

Oh man – this is the biggest spot where I really miss the features of enterprise DBMSs like MSSQL.  There are no stored procs or triggers at the DB level, at least to how you’re used to thinking about them.  Force.com does provide nearly all of the functionality that you’d normally tap via stored procs and triggers, but you have to dive into Workflows (declarative) and APEX classes / triggers (programmatic).

Before you freak out too much – bottom line is YES, between Workflow and APEX, you’ll be able to “proc” away.

In the interest of keeping on task here, I’m not going to dive into APEX on this post – if you want more info, just leave me a comment or hit me on Twitter (@andyboettcher).

Indexes.

This is double-edged sword – I both do and don’t miss being able to index any field.  I was pretty good at optimizing my indexes, but I knew a lot of DBAs who just sucked at it.

Force.com does not do indexing as you’re used to.  There are indexes on certain fields by default, such as:

  • System Time Fields
  • Id
  • Name
  • Any relationship fields
  • Standard sObject-specific
    • Account:  Account Name, Account Owner, Account Record Type, Parent Account
    • Lead:  Company, Email, Lead Owner, Name
    • Contact:  Account Name, Contact Owner, Email, Name, Reports To

Depending on your schema’s needs, you can also contact Salesforce Support if you need indexes on other fields.  I haven’t done this personally, but everyone I have heard of that has done it has been successful (after having a valid and documented use case)

Step 2:  How do I create my schema?  Where’s my trusty Enterprise Manager / Management Studio?

The most raw and fully-functional place you can go is into Setup.

  • Standard Objects:
    • Setup / Customize / (find your object)
    • Fields are located as Setup menu options under each standard sObject
  • Custom Objects:
    • Setup / Create / Objects
    • Fields are located in the “Custom Fields” section on each custom sObject’s configuration page

If you’re feeling really spunky, Salesforce released the read/write “Schema Browser” with Summer ’12 (finally!) that is pretty darned cool.  It’s the closest you’re going to get to an ERD without going to the AppExchange too.

Step 3:  What tips and tricks should I keep in mind while building my Schema?

For the love of everything holy, remember to NORMALIZE!  The double-edged sword of Force.com is that it’s incredibly easy to create sObjects and fields.  Too easy.  Many an instance I’ve walked into with 400 fields in objects that could have been far more properly been done via normalization.  =)

Create a Naming Convention.  Force.com has many examples of how they’ve approached naming conventions (just look through their online documentation or examine their standard sObjects).  Pick one and stick with it.

Along the normalization  lines – think long term and be as generic with field names (as appropriate).  Just because you can maintain seperate “Label” and “API” sObject and field names doesn’t mean you should make a habit of it.  Very little is more frustrating than having to hunt through your schema for a field with two names.


That’s a pretty good start.

Just as with MSSQL or any other DBMS for that matter – your schema is your foundation.  If you have a bad schema, you’re going to have a less-than-optimal application.  Be calm, deliberate, and thorough.  One big thing I try to impart on everyone I talk to about this is just because Force.com is quick and easy – that is no reason to execute that way.  A great saying I heard growing up from my dad while he and I would work on projects around the house – measure twice, cut once.

Questions?  Hit me up in the comments below or on Twitter (@andyboettcher).  Thanks for reading!!

Quick Tip – Getting Prefix of sObject

I had a requirement today to provide two buttons on a Visualforce page which redirected the user to two different list views of sObjects.  This solution also had to go through a number of org migrations so I knew that the sObject prefix would be changing.  Problem?  Nope…getDescribe to the rescue.

Quick and dirty schema query:

public PageReference manageSObjectOne() {

Schema.DescribeSObjectResult dsr = <<API Name of your sObject>>.SObjectType.getDescribe();

PageReference prRef = new PageReference(‘/’ + dsr.getKeyPrefix());
prRef.setRedirect(true);
return prRef;

}

No SOQL hit, nothing except for script statements counting against your governor limits.  Slick.