Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

SharePoint 2010 already has a shoddy (but working) implementation of referential integrity between two lists, where one has a lookup column to the other.

It depends on your application really, if your system will be as complex as you roughly describe, SharePoint Lists may not give the best performance, as the crossjoins incur a short (but noticeable) delay looking up values for each join.

Thus, if you have many joins to many tables with many, many rows, you will very soon have a major performance issue.

Stick to SQL for relational data structures and use the capabilties of BCS with SharePoint designer to interact with that database instead.

share|improve this answer
    
Thanks James. Can you also help out with the following: 1. SharePoint itself contains a hidden ID. Should I create my own ID? 2. 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? –  user1494 Sep 21 '10 at 23:22
    
Use the internal ID field, it's consistent and guaranteed for uniqueness. Use a lookup for foreign keys, again you have SharePoint looking after the referential integrity. –  James Love Sep 22 '10 at 7:43

The SharePoint List ID is guaranteed to be unique but the ID list column field is obviously only unique to the specific list.

The list lookup field has limitations. For instance if you have a lookup column in a list then you need to beware of the "list view lookup threshold" which by default is set to 8 and Microsoft recommends that you don't change it.

In my experience this threshold has prevented me from including more than 9 total columns (1 being a lookup column) in my list view. Depending on what exactly you're trying to do this might be worth considering.

Building a workflow to automatically generate unique IDs as records are created can be helpful in some applications.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.