SharePoint many-to-many strategies

Question

I have a SharePoint site collection that contains a custom site definition and a couple dozen deployed sites for a "Project". Each project contains many "Documents" (each represented by an actual document and a site about the document). So, currently that relationship is one Project to many Documents, and my current sharepoint configuration of each Project site having a custom document library to represent the Documents seems to work fine. My configuration also creates a site for each Document beneath the Project site, with a url like http://myserver/project1/document1.

Now the business requirements have changed, and it now makes sense for a Document to be potentially affiliated with multiple and arbitrary projects. Since each Document is currently in a list within a single Project site, something must change. What's a good way to model this? FWIW, the number of Documents is currently on the order of a couple hundred, with future growth to maybe a couple thousand over the next several years.

My thought is to migrate all of my existing Project-based Document lists into a single global Documents list. Then I'd have to somehow re-create virtual lists within each Project that shows all of the Documents accessible from that Project. It would logically be a filtered view of the global list. Is this a reasonable approach? If so, then...

1. What is the best way to move the Document items and associated sites from within one project and into some global area?
2. What is the best way to generate those Project-filtered views of the global Documents list in a way that automatically accommodates new Project site creation by showing the correct Documents for that Project?

Answer 1

The most sensible way to view a Projects Documents would be a data query based on an attribute on the document, but that relies on a new project automagically knowing/adding values to the Documents it requires to display.

I would be tempted to create a site collection for each Document (and it's site) on a managed path and use a list on each project site to determine which Document sites I look up to retrieve the documents. It is all pretty hard to choose from a distance though.

Answer 2

To automate the creation of sites based on documents i suggest you use an ItemEventReceiver, coupled to to global list, or even better, a custom content type. When the item is added you create a site.

I suggest you also create a ProjectSites list and a DocumentProjectSites list. The list DocumentProjectSites contains two LookupMulti Fields, one bound to the Document Lib, the other to the ProjectSites list.

To fully enable automation, add a site deletion event receiver to the document sites to update the DocumentProjectSites and ProjectSites lists.