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
email
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”.