MOSS - Which would you choose - 120+ additional columns, or a separate linked List?

Question

Hi folks, I have a design conundrum that I could use some feedback on, so I'm hoping my fellow SharePoint experts can help me work this out.

I am managing a set of Projects in a single MOSS List (List1), where the "Project Name" column will be treated as a 'primary key' (at least in my mind) for related Lists. Each Project will have associated with it a set of defined deliverables (up to 37 separate activities throughout each project's lifetime), and each deliverable will track one significant activity that is recommended to complete during the project.

My initial thinking was to define the 37 deliverables in a separate 'lookup List' (List2), so that each deliverable had not just a "Deliverable Name" but also:

"URL" - linking to a separate non-MOSS wiki, where the user can find more info on how to accomplish the deliverable "Description" - to give a quick explanation of what the deliverable means (without having to send the user off to a separate server to get any details) "Project phase" - to help us filter and sort the deliverables into the order in which they're required to be completed "Role" - to identify the primary project role that owns completion of the deliverable

Then I would create individual items in another List (List3), each of which was associated with (1) the Project and (2) the 'template' item in the 'deliverables lookup List', so that we could track additional these per-project/per-deliverable fields:

"Completion date" - date when deliverable was finished (if ever) "How dispositioned" - a drop-down list, from which the user can choose states such as "completed", "postponed", "n/a" or "still in process" "Notes" - free-form text to record more explanations/rationale on what was done and why

The one major problem with this approach is that Microsoft's best practices guidance strongly discourages MOSS Lists with > 2000 items in the List. Even if we never added more deliverables to each Project, I'm afraid that we'll scale beyond 54 projects (which how many I'm "allowed" = 2000/37) in very short order. Creating multiple instances of List3 is theoretically possible, but strikes me as a nightmare to automate (as my set of tracked Projects grows).

The first alternative I can think of is to pre-define 37 additional columns in the Projects list, plus the (37 x 3) columns needed to enable the "date", "disposition" and "notes" fields that the users will need to track for each deliverable. Plus having to manage a brittle configuration/design of each of the SPD Forms & web part Pages that I'd like to use to "pretty up" the UI for all this data entry & data management.

Another alternative someone suggested to me is to create sub-Sites for each Project, and list the project's deliverables in a single List in the project's sub-Site. Seems awfully heavyweight to me, and I'm considering this only as my last resort.

How would any of you pull this off in MOSS (without relying on an outside database, or any code that would have to be installed to the server)? Is there some trick to making this work, that isn't obvious from the usual MOSS List functionality? Is there some hidden feature of MOSS I should use? Some neat aspect of SharePoint Designer I haven't discovered yet? I have to believe that many others have faced this same limitation, and have figured out some way to make it work. I'd appreciate any ideas you folks can suggest - thanks in advance!

Answer 1

In my experience going > 2000 items per container (list, folder, indexed item) is not the end of the world. What is worse, though, is if you start to use multiple lists that are linked to each other (typically through lookups). Then it becomes a big pain when you want to filter a child based on a value in its parent that is not part of the lookup. Reporting on this data can be very slow if you have a lot of records involved in the join.

My inclination in the past has been to use 3rd normal form, but that doesn't work to well with SharePoint lists so I would consider a fairly flat structure. If you have one-to-many relationships you'll mostly want to use separate lists, though.

Answer 2

Microsoft's best practices states that, for performance reasons, you should not have more than 2000 items in a view; You can perfectly well have millions of items in a list, but you should carefully define your views and use of index columns to only return less than 2000 items at one time.

If your list data doesn't change often and you have a lot of memory available on the server, it is worth looking at PortalSiteMapProvider for querying the lists. More info on the various methods to query lists and a full comparative whitepaper can be found here.

Cheers!

Answer 3

I would recommend using subsites or site collections for each project. A meta project site can be defined which can rollup important information for the project sites. The project site can then store more than just the deliverables, but can become a focus for the project collaboration.

MS advice is that performance starts to suffer if you use more than 50,000 site collections, so there is some flexibility there. Sub-sites can swiftly become unmanageable when storing many documents as there is no good way of moving a subsite between content databases when a content database grows too large.

I think the site collection per project architecture will give you greater flexiblity and abvoid the nasty issues with complex lookup lists. It will rely on some fairly clever use of the search results webpart however.

Answer 4

"MOSS Lists with > 2000 items in the List"

this is not a problem as it is just what you show in a view. I think MOSS is not a development plattform to add moe complex items with more fields. I should do a normal SQLserver ASP.NEt application