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 - 2001-07-18 : 00:21:14
|
| E writes "Disclaimer: I searched the site, google, and deja for some insight on this, but I'm not finding a discussion that answers my question.Basically, I want to know the pros and cons of using a multiple-column primary key versus a single-column (possibly autonumber/identity) primary key.The basic database scenario I have in mind is one that basically mimics a multi-user filesystem: you need to keep track of users (in order to know who owns what, who has rights to what, etc.) and keep track of objects (files, folders, etc.). At a minimum, there would be a User table (pk: userID), and a Thing table (pk: thingID, fk: userID). However, I also need tables for a variety of Thing-subclasses, e.g. Folders, Files, Shortcuts, etc., (I guess sort of like horizontal partitioning).So, if I use just a single primary key (thingID) for table Thing, the various Thing-related tables only need thingID as their primary key as well. If I use a multiple column primary key for table Thing (thingID && userID) each of the Thing-related tables of course also need both thingID and userID. This seems to create hassles for the developers working with the database in code. All the joins are more complicated, to traverse up a tree (to find out what folders a file is nested within) the SQL statements get ugly, etc.On the other hand, I am designing for a populous database--lots of users, high user turnover, lots of additions and deletions to table Thing. So, using a single, identity column as a primary key means the keys start getting pretty gigantic pretty quick and over time, the lower range of id's are completely unused right? With enough users (>50,000) performing enough insert and deletes, wouldn't hitting the upper limit of an int column be an issue over time?So... what are some of the significant pros and cons of one approach over the other? --From a developer's perspective, fashioning the SELECTs and INSERTs, from the dba's design/maintenance/performance (possible replication/clustering) perspective, etc.... Or is there another approach altogether? Is one considered a "best practice" over another? Finally, any online references relating to this topic?Thanks!" |
|
|
|
|
|
|
|
multiple column primary keys vs. identity