Developer Forum

Greetings All,

We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.

Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.

Thanks,
Arshad

--
Arshad
XXXX@XXXXX.COM

IDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?

Arshad,

Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:

1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.

The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.

Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.

In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.

Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.

I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.

Hope ths helps and that I haven't started anyone ranting and raving :-)

RD

n Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:


Hi Arshad,

In general, keys can fulfill two distinct functions.

Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.

The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.

My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.

That leaves me with two possible designs:

1. Business key is suitable to be used in the FK relationship:

CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)

2. Business key is not suitable for FK relationship - use surrogate key:

CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)

i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.

Arshad:

Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.

As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.

If you skin is thick enough :>), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal *** from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.

Jeff Clausius
SourceGear

=?Utf-8?B?QXJzaGFk?= < XXXX@XXXXX.COM > wrote in

I couldn't have said it better myself!

-E