When is software too powerful for your own good?

A few years ago I was asked to help a customer with an Excel problem. The employee who originally created a set of Excel spreadsheets that controlled and managed shipping schedules had left the company, and the company now needed a few minor changes… a couple of hours at the most.

After a full day of peeling the onion, I had to break the news that their spreadsheet was as complex as income tax law.  Criss-crossing 3D references between at least a dozen sheets created a database that was beautiful, like the crab nebula, but that could not be maintained or updated without a four digit IQ.

In this case, the company would have been better served:

• Building the database in Access or SQL
• Buying a Line of Business application
• Fixing their processes so that such a complex system wasn’t necessary.

Over-complexity in SharePoint

SharePoint has analogous examples too.  With the use of Custom Lists, External data, Lookup Columns, and Saved Views, SharePoint can act like a simple database, and it works really well- at a certain scale.

When the number of records, the complexity of the relationships, or the detail of each record exceeds a certain threshold, however, SharePoint may no longer be the best fit.

A case study

I was once asked to quote an upgrade project for a SharePoint “database”. The company used InfoPath for inputs, custom lists for storing the data, and custom views for reporting.  For several reasons, they had outgrown their system.

The number of records

SharePoint limits the number of records per view to 5,000. Lists are limited to 30 million total records technically, but there is a practical limit based on your server specifications.

In this case, the SharePoint did not have enough hardware umph to manage the very high number of records in the system. Searching or interacting with the system was painfully slow.

The complexity of the relationships

The company needed tables to interrelate at a level that wasn’t possible with SharePoint.

• SharePoint custom list lookup columns can reference from one custom list, the contents of another, but there are limitations on the way that information in the look up column can be used.
• Certain types of data cannot be brought over in a look up column.
• SharePoint doesn’t have a way to trace or query precedents and descendants of a record. This limits possibilities for building reports.

Upgrade Solution

I proposed to move this client into an Access database with an Access Services front end so that users can interface with it using SharePoint.

Of course development should always be scalable and extensible, but the realities of company budgets, requirements, and culture influence the decision. SharePoint databases grow up into Access databases, which grow up into SQL, which grows up into software development.  Carefully choose what level of development is appropriate for your business need.