Relational Data in SharePoint

Recently I needed to develop a simple subscription system in a WSS 3.0 environment. My client wanted to have a simple event list on which employees could register (with approval).

One of the fantastic 40 application templates addresses this topic, but as I started thinking about it...I wanted to try to make use of the new possibilities of wss 3.0 concerning multiple content types in a custom list. As it turns out my efforts took me quite far!

Let's start by briefly listing the functional requirements:

  1. There needs to be a table/list to store all events. Several different event types exist: internal or external, workshop or course.
  2. Users can register for internal events, an approval workflow is needed.
The obvious choice would be to use a lookup field on the subscription list to link the 2 lists. However, this would mean we have to write some eventhandlers on the first list to ensure that when an event is deleted, the related subscriptions also get deleted. Why not try to reduce the coding by using a different approach.

Since wss 3.0 it is possible to use multiple content types on one list. So all event types can be joined into one list. To ensure that no event handlers need to be written, we'll need to combine the registration and the event list into one custom list. Again this poses no problem: folders can be used to define a relation between an event and a registration.

So the final idea would be to create some content types for the events that inherit from the folder content type. The registrations for each event are stored in a different content type inside the folder of the event.

We end up with a flexible system! As a event gets deleted, all registrations within the folder are deleted. And no coding needed!

Now we only need to define some views, an approval workflow, use some customized CQWP's to ensure a nice layout and we're all set!