Just looking for general feedback --- I've been asked to create a unique key
for a table.  The problem is, it's based on three rather wide attributes...2
varchar(50)'s and a smalldatetime.  apparently this is the only combination
wich comprises a unique entry.  what do you guys think about this?
-- Lynn

Hi

This is to apply a constraint rather than for use as an index, therefore the
business rules imply it should be in place.

John

Show quoteHide quote

Yes, the business rules do imply it's necessity.  I am just worried about the
performance thereof.
-- Lynn


Show quoteHide quote

Hi Lynn

The ideal solution would be to implement this on a test system where you can
stress test it.

John

Show quoteHide quote

I agree with you John but in this case, would it be better for Lynn to
create a unique constraint on those 3 columns and to create a surrogate, one
column, narrow key.
Yes I know, I will receive the wrath of Celko.

Show quoteHide quote

I have nothing against surrogate keys, but this is still a bad idea.  The
unique constraint is necessary to the modeling, and so the only question is
whether the unique constraint should also be the primary key.  It is wildly
premature to say that a surrogate key should be used for performance reasons
(premature optimization).  The simplest logically correct design should be
used unless you can demonstrate a compelling reason not to.

David

On Thu, 10 Nov 2005 10:46:04 -0800, Lynn wrote:

Hi Lynn,

There are basically two choices.

If the table is used to store data, but there are no other tables
referencing this table, then just create a PRIMARY KEY constraint for
these three columns.

If the table is referenced by other tables, then you'll probably want to
add a surrogate key to speed up the joins (though in some cases, even a
referenced table with a wide key can perform faster without surrogate
key). Keep the columns in your table, but add an extra column with the
IDENTITY attribute. Either define the PRIMARY KEY constraint for the new
column and a UNIQUE constraint for the other three, or do it the other
way around. Use the identity column for referencing to a row in another
table. But don't expose the identity column to the end users - use views
that join the referencing table to the referenced table and that show
the two varchar(50)'s and the smalldatetime.

Best, Hugo

convert INT
A difficult Combining Rows problem
How to use OPENXML to retrieve detail lineitems
what is general/conventional way to reduce/prevent deadlock?
Complicated OR