Here's a question that goes along with the original question.I have Contacts, and Groups of contacts. A Contact can belong to multiple groups. Contact Group's Names are unique for each user.I think that the following design will work well and perform well. I guess the question is should I put an arbitrary key (Identity) and a Unique constraint in the Contact Group table, or leave it like it is?? CREATE TABLE #Contact ( [ContactID] [int] IDENTITY (1, 1) NOT NULL , --PK [UserID] [int] NOT NULL , [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) CREATE TABLE #ContactGroupContacts( [UserID] [int] NOT NULL , [GroupName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ContactID] [int] NOT NULL )CREATE TABLE [dbo].[ContactGroup] ( [UserID] [int] NOT NULL , [GroupName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [SortOrder] [int] NOT NULL , [Cre_Date] [datetime] NOT NULL , [Cng_Date] [datetime] NOT NULL )CREATE UNIQUE CLUSTERED INDEX [PK_ContactGroup] ON [dbo].[ContactGroup] ([UserID], [GroupName])
Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> 
Composite Key == Natural Key
Posted - 2002-11-06 : 13:47:16
