“SharePoint Is Not a Database”

This is something that I’ve told people more times than they really want to hear.  Sure, SharePoint sits on top of SQL Server, which is an excellent database, but SharePoint itself is not a database!  So is this a dire “abandon all hope, ye who enter here” message?  Absolutely not.  With planning and forethought, you can build quite nice applications in SharePoint that act in a database-like way.  What it does mean is that all of the old rules still apply.

When I think about this, I’m always reminded of the Rules of Data Normalization poster from Database Programming & Design magazine by Marc Rettig which was hanging in one of my colleague’s cubes in the late 1980s.  (Through the miracle of the Web, I was actually able to find this picture of it!)  Though I can’t recite the rules from memory, it has informed my database and now custom list designs to this day. 

1. Eliminate repeating groups
2. Eliminate redundant data
3. Eliminate columns not dependent on key
4. Isolate independent multiple relationships
5. Isolate semantically related multiple relationships

I’m not going to go into the details of what each of these rules means (a quick search on the Web or an introductory Computer Science course will explain them to you).  These rules and the nice 80s colors are cool, but what does this mean when you develop SharePoint applications today?  Well, quite a few things.

1. First and foremost, make sure that you build your lists following these rules.  One key flaw in SharePoint’s architecture (it’s there for good reasons having to do with Office compatibility) that forces violation of the rules is the Lookup Field Type.  Rather than storing the ID of the referenced item, the text value is stored.  Try to avoid using the Lookup Field Type directly and…
2. Build custom forms to replace Newform.aspx and EditForm.aspx that enforce these rules.  The default forms contain a single List View Web Part, which works very well in creating and editing items in a standalone list.  When you want lists to interrelate, you’ll want to build custom forms.  These custom forms can contain multiple Data View Web Parts (DVWPs).  A DVWP used as an input form can only interact with a single list, but you can have the DVWPs interact using hidden columns and JavaScript.  (Yes, you can write custom Web Parts here, and I’m not discouraging that, but in many cases managed code overcomplicates things.)Rather than using Lookup Field Types, you can display the available values from the lookup list(s) and capture the IDs in selects or with radio buttons, etc. which you build yourself in DVWPs.  With JavaScript on the events, you can then “stuff” the chosen values into the appropriate columns in the target list.Several notes on custom forms:
   * Don’t edit the default forms directly — take a copy and change the copy.  You never know when you might want to revert to the original form.
   * Change the forms associated with the list by right clicking on the list container in SharePoint Designer, Properties, Supporting Files tab.  Making the change here will cause a redirect to your custom form even in places that the original form is requested.
3. Handle deletes elegantly.  There is no hook for workflows in SharePoint Designer to trap on a delete.  However, if you build your forms well, you can provide delete capabilities that you can trap on and handle.  There’s no such thing as cascading deletes in SharePoint lists (it’s not a database, remember?), so you’ll need to do the work yourself.  If you’ve followed the data normalization rules well, this should not be too onerous a set of tasks.  One simple way to protect yourself is to just disallow deletes in lookup lists.  This means that you will need to provide some administrative function to handle them, but you will avoid simple breaks to the application.  Be sure to…
4. Teach your users the value of all of this.  If you build your forms well, the amount of education ought to be minimal, but you may want to explain your methods so that they understand how it all holds together.  Why?  Well, if they don’t understand, they may try to do things to get around your good planning, which will make it all fall apart.

I’ve written many posts in the past that have snippets of how to do each of these things, so if you have questions about any of the details, take a troll back through my old posts or shoot me your questions.