SharePoint does not store data in a truly relational manner. One-to-many relationships can be handled by storing an identifier from the primary list with the corresponding records in the foreign list. Ideally this would be the "ID" field value, but this makes for a less-than-ideal user experience since they'd be responsible for typing in numbers that mean nothing to them. It is also a recipe for redundant data, especially when you have a compound primary key (i.e. more than one field in a list is required to uniquely identify a particular record).
Our first solution for this would be: We need a SharePoint custom field named PrimaryKeyField to check our users input against the other values from the same colum in the list, to verify is it unique, or not? Ok, we have implemented this custom field now and inside SharePoint we are abel to select it if we will add a new colum to a list:
In the next version we will rename this to 'Unique single line of text' and will find a solution to change existing StringFields to this new type, may be we are able to change the title field in the future also.
We now use our new field and give him a name in our custom list named Customer:
In the meantime we have filled our Customer list with three items:
Another Sharepoint user will add also an item to our list in his browser, bacause he is allowed do do this, but he has always little personal problems to oversee all..
Now we have solved the users problem with his glasses ;-) and will hope he is understanding the message. But our problem is not solved, only a small item on it.
Our next step after this, will be, to do the same on the new event handler for lists. We have now in SharePoint v3 an event on before the item ist saved to the list, but at first we have found that we have no access to the custom field item, or what is wrong? Must be digg deeper? ..