SharePoint List Best practices

Question

Hi all, I am creating an application using SharePoint List for storing the data. The data design would pretty much similar like if you are going to put it in a relational database, with items like many to many relationship, primary keys, foreign keys.

I would like to know what are the best practices?

There are a few questions that I have in mind already when creating the list:

• SharePoint itself contains a hidden ID. Should I create my own ID?
• What's the best way to reflect a foreign key? Is it just as plainly creating a lookup field? or do you think it's best to just create a single line text and then my program will link the ID. Which one to use which?

Any other tips on this would be helpful. Btw, I am using SharePoint 2010.

Answer 1

I would not do this. If it is going to perform like a relational database, you should just use a relational database. The problem you are experiencing with foreign keys is only the tip of the iceberg when it comes to using lists as databases tables.

Answer 2

I wouldn't go there either. Especially not through the BDC. If you have to integrate with relational data you might as well do that directly from your .NET workflow code. You could still leverage SharePoint for initiating workflows and maintaining Tasks lists, etc.

If your data is relational.. put it in a relational database. It's good with that.

Answer 3

I totally agree with the other guys. Relational data is best placed in a database that is designed for that.

Since you are using SharePoint 2010 you have a bit better support for handling big lists by the means of throttling and improved lookup fields. Also the BDC is now called BCS and supports updating and creating data by the means of SharePoint lists. In theory this gives you two more options: a. Put the data in lists using look-up fields and b. Put the data in the database and access it via BCS.

Approach a) is for sure only durable as long as your lists do not have more than a couple of thousand entries and might have some issues due to the nature of look-up fields. For approach b) I have no idea how BCS deals with relational data. I have been using it with a simple unrelated data from a database and that just works fine. Also for big amouts of data. Although both approaches have their drawback they still have the advantage that you get the user interface and security trimming 'for free'. I think it depends pretty much on your scenario what makes best sense...

Answer 4

I agree that SharePoint lists are no replacement for a relational database. If you're heart-set on having the data located within SharePoint, perhaps think about using Access services to create your entity-relationship diagram and then publish the database to SharePoint using access services.

Take a look at these Channel 9 videos for more details of the possibilities with access services

http://channel9.msdn.com/Learn/Courses/Office2010/AccessUnit

Answer 5

In some cases you would like to store your data in a database rather than in SharePoint lists, if you are sure that this is what you want to do then you create your database and tables as you would normally do, and then create a list using an External Content Type that you define in SharePoint to reflect your data in your database.

More info on the subject here: http://msdn.microsoft.com/en-us/library/ee557243.aspx