How to store and maintain column data with relationships

Question

We have a requirement to store metadata for documents, but the complication comes because:

• metadata has relationships (many-to-many)
• Its a multi select
• cannot use Profile Store (My sites) as this is not scope and the enterprise only for a department.

Example Metadata

We have two entities:

• Subject
• Courses

The relationship is many-to-many because one subject can be part of 'n' courses and one course can have 'n' subjects.

Scenario

When a user uploads a document, they can select multiple subjects and multiple courses (the selection should be guided and cascading based on course selection - possibly a custom screen.)

When a student registers to the site and selects their respective courses and selected subjects, the system should show relevant documents and all the documents should be searchable using metadata (an interface like faceted search)

My Questions

• What should be my approach to store and maintain custom lists/custom SQL DB? As there are relationships.
• Can we store all the documents in one Document Library? (Limitations)
• What is the best way to store student preferences and personalize the content based on the preferences. (show only documents related to subjects and user preferences)

Answer 1

I am assuming you're using MOSS or WSS 3.0 and not SPS/SPF 2010 as this opens up a whole other managed metadata scenario.

You are going to want two lists for your subject and courses metadata, and two matching lookup site columns for these lists.

These can then be used in your document library(ies) as custom columns.

The cascading lists can be achieved using SPServices more info on that here: http://spservices.codeplex.com/wikipage?title=%24%28%29.SPServices.SPCascadeDropdowns&referringTitle=Documentation

You could store all the data in one document library assuming you have appropriately managed the number of list items (assuming more than 2/3000 items) more info on this here: http://office.microsoft.com/en-us/sharepointtechnology/HA101736671033.aspx

You could use an additional list to store the users preferences. This could have columns containing the user and the relevant subject/course IDs. This can be used in many ways depending upon your skill and preference. I would probably crack open Visual Studio and create a web part as I am a developer. However increasingly I am leaning towards the SPServices AJAX approach in lots of scenarios.

What you have provided here is the bare bones of the requirements for a reasonably sophisticated solution. It is not as simple as one answer fits all I am afraid, but I hope this at at least helped a little.

UPDATE

The lookup colums would allow you to populate the listbox (as this is a multi select), what happens when those items are selected is up to you. This is where some Javascript could be applied, but you are going to need to come up with a mechanism for storing metadata against user names (or something similar). That is what the 3rd list would be for in my example.

Its worth noting that you could use a SQL table or other data store, but the code to add a list item is provided by the web services which are easily wrapped with SPServices, so this can all be done asychronously (AJAX - the silent wonder!)