In every major project we are running, there is always a request to store some additional metadata to SharePoint. Usually this data describes :

• processes running on SharePoint platform
• document attributes
• users

…or customer just wants to have the same, familiar web interface or a single web application for the end user.

Few days ago I stumbled upon a discussion on this topic at StackOverflow (learn more about StackOverflow). My response is currently marked as the correct one :).

In this post I will try to give some additional details:

Using SharePoint lists to store meta-data

Here are the key benefits why you should use SharePoint custom lists to store data. These features are hard to build from scratch but are available within all SharePoint editions:

• Edit in datasheet / Excel / Access
  This is probably the most important benefit of using SharePoint as data store for the metadata. Users just love to edit their data from web-based datasheets. It is easy to use and updates can be done in seconds instead of minutes.
• Check-in & Check-out
• Versioning
• Permissions
• Ease of use for end users
• Workflows
• Search

Using SQL database to store metadata

In some scenarios SharePoint custom lists will not be adequate and you will need to use SQL Database to store data. In a scenario like this you will need to create a custom database to store data. Here are some cases when you should do it:

• You have a many-to-many relationship in your database model
  These kind of relationships are problematic because you will need to customize UI in order to allow user to easily join two entities. The built-in interface works OK if there are only a few values on each side, but for couple of hundreds of items it won’t be too useful.
• Two or more entities are linked together (Customer > Invoices > Invoice Products)
• SharePoint lists are great when an entity is linked to another entity (One-to-Many relationship). But if you want to link two or more together (in series) this might be tricky and is not supported out-of-box.

For both scenarios mentioned above you will probably want to display data in master > detail format and that is not easy to achieve.

• Reporting
  If you plan to create reports or charts about metadata, stick to plain SQL database. It is much easier to create Views over your own plain data then the SharePoint data (this is also not supported, you should always access SharePoint data via API).

You should definitely consider Business Data Catalog as a solution to integrate your custom data with SharePoint. It is easy to use and no programming required. Bear in mind that it is only availabile with SharePoint Enterprise CAL. If you are using SharePoint Standard CAL or Windows SharePoint Services 3.0, then BDC is not availabile.

For such scenarios you can either develop your own solution or use a 3rd party solution like Bamboo MashPoint or some other solution by Bamboo, LightningTools and other vendors. All these solutions have pros & cons, so before embarking a project do some prototyping to avoid problems.