Database Normalization And Salesforce - Ponderings

One of the biggest challenges in moving from a highly structured Oracle environment into Salesforce is how denormalized Salesforce's querying / reporting / data-viewing limitations encourage one's data model to be.

This particularly seems to be an issue in higher education. Like banking or healthcare, most of its data is rigid, with the typical business user's number one concern being easily expressed as, "Please don't mess up my data." Overall, higher education's business needs beg to have a university's databases insanely normalized.

Except on the fringes, that is. On the fringes, a university's interactions with the outside world stop being about individual human beings who care about "their" data "not being messed up." Prime "fringe" examples include:

1. Email-address-based marketing, which is free and great, but hard to pin down to an actual person because people share
2. Continuing education and seminars, where it's not really a big deal to swap attendees - if it's corporate training, who at the sending company cares who comes as long as someone gets trained?

These "fringe of the university's business" use cases, plus staff's desire to see the two sets of data married, seem to tempt universities to look at databases beyond their 20-year-old ERPs. (Of course such "data marriages" also desire the tall order of doing so without polluting vital information like the admission-graduation pipeline, employment history and paychecks, donation history!)

• Unfortunately, this marriage means you're back to squeezing "fuzzy" data into a normalized structure. Which, in theory, you could do in your ERP if it gave you an easy way to add tables (and user interfaces to those tables).
• Then there's the route of struggling to squeeze highly-normalized "vital" data into a "flat" structure like Salesforce encourages. Unfortunately, this can mean you're always fighting inaccuracy, lag, duplication...

Integrating Salesforce and our ERP (plus a few new data stores) is a much longer road than I was expecting, and it all keeps coming down to issues with matching a data model to the real world.

Banner is an amazing set of highly normalized tables for "don't lose my data" core university functions.
(If you get a look at the EnrollmentRx plugin for Salesforce as a Banner user, you'll be amazed - it's basically SPAIDEN/SRARECR/SAAADMS/SOATEST built into a newer database and restricted to "1 Contact/SPAIDEN record per email address." Everyone re-invents the wheel for higher ed because, like banking, some things just don't change.)

I think Sungard/Ellucian really missed the boat here.

They could have raked in the big bucks if they'd "taken care of the fringes" and:

1. Given Banner just a few tweaks for easily adding/removing tables (and corresponding user interfaces)
2. dded a good way to upsert the database 1-record-at-a-time
   (e.g. web forms - as FormAssembly's Salesforce connector proves, you really just need to have a user interface that lets a form-handler-configuration-managing user build complex SQL queries sorted by LastModifiedDate descending - not everything has to be "common-matched" - similar idea for fast deduplication ... DemandTools, which is to PL/SQL what Cognos is to SQL, exists because a good CRUD API to the Salesforce database exists)
3. Engineered a great "over"-layer for email tracking and sending - especially by admissions departments.

For example, what if there were some sort of API that let you quickly create/update/delete SPACMNT records from what's otherwise basically a Cognos report? Like, a little pencil-shaped "edit" button near the list of comments, but otherwise, nothing is editable - it's just the read-only report (doing nice things like hiding details of inactive records and only showing you the most-recent SRARECR/SAAADMS/SGASTDN data available, as determined by the report-author)? Or a little "edit" button next to "status" that lets you pick from a picklist, and then when you commit it, the "interactive report" author has programmed logic to update SRARECR/SAAADMS/SGASTDN accordingly? That's basically what one of our main departments wants. A "collapsed, current, relevant" view of dispersed back-end data, with a few "edit" buttons interspersed so they don't have to surf through the normalized tables themselves to make updates.

The other major reason we tried Salesforce was because it had plugins for the web/email era and Banner didn't. (And a final reason was to accommodate those "fuzzy"-work departments that were using spreadsheets, Access databases, Rolodexes, etc.)

I often wonder where we'd be if we'd been able to do all this development straight into our highly-normalized ERP instead of half-rebuilding Banner inside of Salesforce.

What would we have done if we could have web'd/emailed/custom-tabled/easy-writeback'ed our existing database + data model instead of replicating our tried-and-true data model inside of Salesforce? What could we have done if Ellucian had tacked on the user-friendly concepts that Salesforce made popular instead of leaving their customers in the 1990s?

Salesforce's good luck and Ellucian's loss, I suppose. But I wish I often wish I could have my cake and eat it too - full normalization support and modern-database flexibility.

Since I can't, I still believe that the ideal for universities is to roll out Salesforce "from Rolodex to ERP," not "from ERP to Rolodex."