Just Because They Are SharePoint Lists It Doesn’t Mean Good Database Rules Don’t Apply
Matt Bramer and I were helping someone out in the Stump the Panel forums at EndUserSharePoint.com the other day and in the conversation, the person posted this list structure:
I created a list called “Solution Team Profiles” with the following columns
affinity to market
birth date
Bus Phone
Cell Phone
Clearence Levels
Deal 1 Allocation
Deal 1 Response Date
Deal 1 SiebelID
Deal 1 Status
Deal 1 Title
Deal 2 Allocation
Deal 2 Response Date
Deal 2 SiebelID
Deal 2 Status
Deal 2 Title
Deal 3 Allocation
Deal 3 Response Date
Deal 3 SiebelID
Deal 3 Status
Deal 3 Title
Deal 4 Allocation
Deal 4 Response Date
Deal 4 SiebelID
Deal 4 Status
Deal 4 Title
Deal 5 Allocation
Deal 5 Response Date
Deal 5 SiebelID
Deal 5 Status
Deal 5 Title
Deal 6 Allocation
Deal 6 Response Date
Deal 6 SiebelID
Deal 6 Status
Deal 6 Title
deal size
deal turn
employee
employeeid
Anniversary
location
netid
Other Allocation
other skills
Other Status
Other Title
primary expertise
role
secondary expertise
svc yrs
Team
Today
utilization
Vacation PLans
Created By
Modified By
I pointed out that since he had repeating groups, he really ought to be storing the deal information in a separate list. Matt pinged me with a question on why I made that suggestion.
I could go on and on about database theory, but the bottom line on this one is that is you find yourself naming columns in a SharePoint list something1, something2, something3, etc., you are violating relational rules. This would be what is called a “repeating group”, and that stuff should go into its own list. The best example of this is that of an order you place with Amazon.com. You might order three or four things on that order, right? So [very simplistically], you’d have one list which contained information about the order itself: order number, date created, number of items, ship to address, total value, etc. Then in a separate list, you’d have an row per item ordered: order number, line number, quantity, description, cost, extended price, etc. (Note that with today’s highly efficient databases, you might not store the totals information in the orders table because it’s something you can calculate on the fly if you need to from the details. When I learned my database stuff, you’d never do that because it was too expensive.)
Storing the repeating groups in a separate table means that you aren’t reserving space in the orders table which may or may not be populated, therefore, wasting space in the database. It also makes the orders table more efficient because in many cases, you’ll only need to query that primary table. For instance, “How many orders has Matt placed?” can be answered by looking only at the orders list. When you need to look at the order details, you can join to the detail list to get at that information, using the order number as the key.
If this all sounds like gobbledy-gook to you, pick up a beginning database theory book and skim it. It’s all pretty simple stuff once you get it, but even seasoned vets make mistakes about this stuff all the time. There is a set of database normalization rules (several of which I glossed over in the example above) which, if followed, will ensure good database structures which are efficient and can also meet all of your needs.
Just because SharePoint lists aren’t really database tables doesn’t mean that you shouldn’t try to follow good database design rules, as I pointed out in my blog post entitled “SharePoint Is Not a Database”.
Marc, I happened to just have a similar conversation just the other day! I was being asked to extend a project by adding a few more items to the list and I wanted to know what they wanted to do. They wanted to track a certain type of revision to an item and then be able to metricfy these changes. They thought they would just add up to ten more fields or changes to the list and I had to stop that idea and show them why moving it to a new list would be better. I actually do this all the time. Great post!
Having done most of my programming before SharePoint in either Access or a little bit of SQL Server, the impulse to normalize is pretty strong with me. What I’m struggling with right now with SharePoint is the front-end stuff. If there’s one thing I fervently wish I could bring over from Access, it’s subforms. Right now I’m trying to build an app in SharePoint that, if properly normalized, would have at least three levels, i.e. Table A has a one-to-many with Table B, which has a one-to-many with Table C, which has a one-to-many with Table D. In Access, this is simple to handle: just nest subforms for each of those and you’re set. In SharePoint, I’m flailing, because I don’t know how to approach the problem.
Carlos:
You can do similar stuff with SharePoint forms, but the mechanisms are quite different. In fact, you have lots of options, but they will all require customizing the forms to some degree.
Forms simply have List Form Web Parts (LFWPs) on them which automagically show you all of the list’s columns with the right input elements. You can replace the LFWP with a Data View Web Part (DVWP aka DFWP) and have total control over the form. You can only interact with one list per form, but you can have multiple forms per page, much like you are used to.
Also think about chaining your forms together more wizard-like to accomplish your goals. The SPRedirectWithID function in my SPServices library can help with this.
M.
Thanks Marc. I think the wizard style interface will be the way I’ll go and since I’m already using SPServices for other stuff already (mainly SPCascadeDropdowns and also the wrapper for GetListItems a couple times), I’m halfway there already. I guess I just miss the ease and convenience of being able to fulfill the requirements with a strokes of a mouse instead of hours and hours of coding.
Keeps me employed, though, and I’m not opposed to that.
Carlos:
Once you get the hang of it, you might even like it better. ;+)
M.