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

1 Answer 1

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

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.