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!!

Advertisements

3 thoughts on “Transition from MSSQL to Force.com

  1. Andy, I’m been thinking on the appropriate amount of Normalization on Force.com. Its a tricky topic to be sure. There are several items you need to take into account when building out your data model. Denormalizing the structure and using Record Types plays well with SFDC standard page layouts and consumes less managed storage, however normalized structures help represent the entities properly and allows you advantages of one record per piece of data.

    What are your thoughts on the right level of Normalization / Denormalization? I know when I run Data Model sessions we focus on the business processes and entities, and work our way from there. Its a struggle to find that right level of balance between Normalization and Denormalization. How have you explained that to traditional DBA’s you’ve worked with?

    • An “appropriate amount” of normalization is definitely in the eye of the beholder, that’s for sure. =)

      In summary – you have to let them fail at Force.com. Read on….

      IMHO, the eternal struggle regardless of platform is always between designing for high transaction throughput / optimization and providing a base for reporting that doesn’t require fifty join statements and sub-reports on top of sub-reports to get out useful information. (classic example is SAP – awesome transactional speed, but you really need a separate reporting warehouse to get useful reporting data out)

      Things like using the traditional master/child relationships is definitely without-a-doubt necessary, but the best over-arching advice that I have been able to come up for any Force.com newbie is to still approach the ERD with the same “should I normalize this” thoughts, but then look again at what Force provides to let both normalization sanity and reporting needs.

      Force.com presents an interesting paradigm when compared to a more traditional DBMS environment. It’s definitely a challenge for incoming alt-system DBAs to rethink traditional normalization lines. The more entrenched career DBAs sometimes just can’t change their way of thinking.

      The kicker is really you have to let them fail. Force is an entirely different beast…Oracle? MSSQL? MySQL? You don’t have limits on indexes, fields, and relationship data navigations…heck, even just the loss of stored procs is enough to make any DBA think twice about doing Force.

      Every DBA does things just a little different. The best thing I can think to do is to let them just try. Spin up a Developer Edition org and let them try. Odds are they will run into something that they will want to rake their eyes out over – that’s where you step in and introduce how to work within the Force.com framework against what they tried.

      That, and a lot of beer. =)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s