Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-21 : 08:27:41
|
| Bhavesh writes "wellhere is the problemfollowing are the tables i m using1. Student (StudentID INT Identity - PK, FN, LN)2. Address (AddressTypeID INT Identity - PK, AddressTypeDesc)3. StudentAddress (StudentID, AddressTypeID, Add1, Add2, City, State, Country, Zip)PK is StudentID+AddressTypeID - compositeStudentAddress.StudentID refers Student tableStudentAddress.AddressTypeID refers Address tableThere is one person in my team, strongly opposing this designAccording to him, the table "StudentAddress" should have one more field, StudentAddressID as INT, and this should be PK, instead of the combination of StudentID+AddressTypeID, and this combination should have a UNIQUE ConstraintBut this is not required at all, there is not harm in having the combination as PK, and that too, both the columns are INT. This combination is the natural PK, and no need to have any extra column as PKWhat do u think about this, please give ur opinion....Thank you all" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-21 : 08:47:09
|
| Given the details provided, I'd say the natural key (StudentID+AddressID composite) is a much better candidate for the Primary Key than a synthetic/contrived/surrogate key(StudentAddressID IDENTITY property). Unless you have some other table that needs to reference StudentAddress in a FK constraint, and I can't even think of an example, the StudentAddressID is just extra data that does nothing but bastardize the relational model....You're gonna want you're clustered index on the composite. You're gonna put a unique constraint on the composite. It's the primary key, plain and simple.<O> |
 |
|
|
|
|
|
|
|
db design : Identity Column as PK or composite PK