What datatype to use for PK?

[Don Vaillancourt]

Over the years I have always used the decimal(18,0) as the datatype for
primary keys. Aside from the number of significant numbers involved,
would BigInt type be better for performance or is decimal(18,0) still okay.
--
Don Vaillancourt
Director of Software Development
WEB IMPACT INC.
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.com>
web: http://www.web-impact.com
Web Impact Inc. <http://www.web-impact.com>
This email message is intended only for the addressee(s) and contains
information that may be confidential and/or copyright.

If you are not the intended recipient please notify the sender by reply
email and immediately delete this email.

Use, disclosure or reproduction of this email by anyone other than the
intended recipient(s) is strictly prohibited. No representation is made
that this email or any attachments are free of viruses. Virus scanning
is recommended and is the responsibility of the recipient.

[Greg D. Moore \(Strider\)]

"Don Vaillancourt" <do**@webimpact .com> wrote in message
news:Ci******** ************@nn rp1.uunet.ca...

Over the years I have always used the decimal(18,0) as the datatype for
primary keys. Aside from the number of significant numbers involved,
would BigInt type be better for performance or is decimal(18,0) still

okay.

I think it depends on what your primary key is. If it's an ISBN for
example, I don't think either of these would work.

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

Why is that an advantage? If two rows represent the same physical
entity then you usually want to be able to identify them as being the
same - otherwise you end up with unwanted duplicates. That is why
natural keys are important. Billing the same customer twice isn't
usually good for business!

As long as the customer pays both bills it is!

As for the use of artificial keys - if the thing inserted into the database
is an order by a web customer, there is no natural key until it has been
inserted. The order-entry system must generate the key itself.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Gary (ga**********@w cc.ml.com) writes:

Erland, where did you get that typedef code? Is that from SQL Server
source code?

The OLE DB Refeference Manual in the MDAC Books Online. It's also in MSDN
Library.

Of course, that's a general interface which is independent of the data
source, but I would not be surprised if the format is taken from SQL
server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

A natural key is simply a product of the way some data is represented
in a table - a subset of the attributes that you need to record. In the
case of an online order that might be (user,order_dat etime) for
example. A natural key is never "generated" internally because it
always represents something that is "outside the system" - that is the
critical distinction between a natural and artificial key.

--
David Portas
SQL Server MVP
--

[Mike Sherrill]

On 9 Feb 2005 14:00:03 -0800, "ta*********@ho tmail.com"
<ta*********@ho tmail.com> wrote:

[snip]

Also, When i use GUID for pk i can mix together values from different
tables and still have a unique column within the view. for example I
can grab address info from both the Customer and the Vendor tables to
create a view that can return all addresses.

I'd like to see the conceptual model behind that.

--
Mike Sherrill
Information Management Systems

[David Portas]

Conceptually the model is a "bag" rather than a "set" i.e. duplicates
are allowed (the GUID isn't part of the logical model so it doesn't
count). A bag may be useful for a table in a "staging" database as part
of a data transformation process. The transformation then reconciles
and eliminates duplicates so that integrity is maintained.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

A natural key is simply a product of the way some data is represented
in a table - a subset of the attributes that you need to record. In the
case of an online order that might be (user,order_dat etime) for
example. A natural key is never "generated" internally because it
always represents something that is "outside the system" - that is the
critical distinction between a natural and artificial key.

The natural key for an order - the way users would refer to it - is
the order id. In a paper-based computer-less system, it would be a
pre-printed number on an order book. In a computer-based system, the
order id is generated by the system at the time for the order.

Involving userid is likely to be out of the question in many middle-
tier apps - all orders from the same user. Customer may do better, but
it may be perfectly legal for the same customer to place two orders at
the same time. For instance, the customer may be a company, and two
different departments are making different orders.

And if even if you could construct this natural key, I can tell you that the
users blissfully will ignore it. What they need is an order id, and you
must somehow report that back. In a web app, you sometimes want to avoid
an extra rountrip to get that number. Sending down a GUID from the client
is one way to handle it. A GUID is a poor key for usability, but if it's
reported back to another app in a business-to-business solution, this is
less of an issue.

The idea with a "natural key" as being something "outside the system" may
sound nice, but it does not model the reality for real-life system.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

> Involving userid is likely to be out of the question in many middle-

tier apps - all orders from the same user
True. There may be a difference between "user" and "customer". Customer
is what I meant. Customers do indeed require an order number generated
by the system - no problem there.
it may be perfectly legal for the same customer to place two orders at the same time. For instance, the customer may be a company, and two
different departments are making different orders.
Say, two orders are placed simultaneously by different entities
(departments or whatever) and you don't record the information that
distinguishes them then how will you know which is which? The
artificial order number doesn't tell you that. In your hypothetical
scenario wouldn't the order confirmation emails go to the same address?
So even the customer's Accounts Payable department wouldn't know which
items on the order belonged to which department unless the end-user
recorded that information separately. Is there a benefit in issuing
multiple order numbers in this scenario? I'm not sure, but if there
were and you wanted to persist information about different, arbitrary
"sub-groups" of order items with all other attributes of the order
identical then in 3NF that information belongs as an attribute in the
Order Details table, not as a duplicate in the Order table. That's
unless the "department " attribute was recorded as well, in which case
Department would obviously become part of the Order table's key.
The idea with a "natural key" as being something "outside the system" may sound nice, but it does not model the reality for real-life system.

I thought "real-life" was "outside the system" but maybe your
definition of reality differs from mine. :-)

--
David Portas
SQL Server MVP
--

[Mike Sherrill]

On 10 Feb 2005 03:28:00 -0800, "David Portas"
<RE************ *************** *@acm.org> wrote:

Conceptually the model is a "bag" rather than a "set" i.e. duplicates
are allowed (the GUID isn't part of the logical model so it doesn't
count).

Not exactly where I was going, but it's in the same direction. I was
using "conceptual model" in the sense Halpin uses it in ORM. And I'd
still like Tal to post it.

--
Mike Sherrill
Information Management Systems

[Don Vaillancourt]

Well no, the PK would be an integer.

Greg D. Moore (Strider) wrote:

"Don Vaillancourt" <do**@webimpact .com> wrote in message
news:Ci******** ************@nn rp1.uunet.ca...

Over the years I have always used the decimal(18,0) as the datatype for
primary keys. Aside from the number of significant numbers involved,
would BigInt type be better for performance or is decimal(18,0) still

okay.

I think it depends on what your primary key is. If it's an ISBN for
example, I don't think either of these would work.

--
Don Vaillancourt
Director of Software Development
WEB IMPACT INC.
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.com>
web: http://www.web-impact.com
Web Impact Inc. <http://www.web-impact.com>
This email message is intended only for the addressee(s) and contains
information that may be confidential and/or copyright.

If you are not the intended recipient please notify the sender by reply
email and immediately delete this email.

Use, disclosure or reproduction of this email by anyone other than the
intended recipient(s) is strictly prohibited. No representation is made
that this email or any attachments are free of viruses. Virus scanning
is recommended and is the responsibility of the recipient.