Posted by Hugo Kornelis on 10/01/54 11:42

On 15 Mar 2006 08:51:40 -0800, pb648174 wrote:

>Just out of curiosity, lets say we are told that only a basic
>description of something is needed, let's say a list of projects that a
>
>user is going to work on, that needs to be 500 characters long. What
>should the primary key be? Is it the 500 character description or an
>identity column? Are there performance issues with having 500 character
>
>foreign keys, indexes, etc.? In what ways is it going to be better than
>
>using an identity column taking into account that it is going to be
>passed through the QueryString, posted in links, etc.?

Hi pb648174,

CREATE TABLE Projects
(ProjectId int NOT NULL IDENTITY,
ProjectName varchar(500) NOT NULL,
PRIMARY KEY (ProjectId),
UNIQUE (ProjectName)
)

The ProjectId can be used in referencing columns. The ProjectName is the
column that is used as key externally; the UNIQUE constraints is what
enforces the unicity of the business key. No user should ever get to see
the PrjoectId value. All reports showing projects should join to the
Projects table and usse the ProjectName column. This is easily
accomplished by setting up the appropriate views and restricting access
to the underlying base tables (or, better yet, use stored procedures
only).

--
Hugo Kornelis, SQL Server MVP

[Back to original message]