Em Qui, 2006-01-19 às 22:29 +0100, Martijn van Oosterhout escreveu:
Possibly nowhere. But when you send invoices to customers, any details
on there *are* immutable. Sure, in your database you don't care if
things change, but then they don't match reality anymore do they?
Then what you
On Thu, Jan 19, 2006 at 00:06:41 -0500,
Tom Lane [EMAIL PROTECTED] wrote:
The problem with SSN is that somebody other than you controls it.
If you are the college registrar, then you control the student's
registration number, and you don't have to change it. In fact, guess
what: you
On Thu, Jan 19, 2006 at 09:53:11 -0500,
[EMAIL PROTECTED] wrote:
Yes. Representation of the DNA is probably best. But - that's a lot of
data to use as a key in multiple tables. :-)
On a simple level, this would be a problem for twins.
There are other complications as well. People are going
On 1/19/06, Pollard, Mike [EMAIL PROTECTED] wrote:
Martijn van Oosterhout wrote:
Please provides natural keys for any of the following:
- A Person
- A phone call: (from,to,date,time,duration) is not enough
- A physical address
- A phone line: (phone numbers arn't unique over time)
On Thu, Jan 19, 2006 at 12:06:41AM -0500, Tom Lane wrote:
Michael Glaesemann [EMAIL PROTECTED] writes:
As far as I can tell, the only difference between your position,
Dann, and Date and Darwen's, is that you think no natural key is
immutable.
DD's examples of natural keys are worth a
On Thu, Jan 19, 2006 at 12:50:52AM -0800, David Fetter wrote:
On Thu, Jan 19, 2006 at 12:06:41AM -0500, Tom Lane wrote:
No, that's not the big problem. The big problem is that it's very
likely illegal for you to use it for anything unless you happen to be
the Social Security Administration.
Martijn van Oosterhout wrote:
Please provides natural keys for any of the following:
- A Person
- A phone call: (from,to,date,time,duration) is not enough
- A physical address
- A phone line: (phone numbers arn't unique over time)
- An internet account: (usernames not unique over time
On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
Martijn van Oosterhout wrote:
Please provides natural keys for any of the following:
- A Person
- A phone call: (from,to,date,time,duration) is not enough
- A physical address
- A phone line: (phone numbers arn't unique over
On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
The point? Surrogate keys and natural keys are two tools in the
database arsenal. Just as it is unwise to use a hammer to drive a screw
just because you don't believe in screwdrivers, it is unwise to just off
hand discard either
Martjin,
In any of these either misspellings, changes of names, ownership or
even structure over time render the obvious useless as keys. There are
techniques for detecting and reducing duplication but the point is that
for any of these duplicates *can* be valid data.
Please point me out
Jim,
So ISTM it's much easier to just use surrogate keys and be
done with it. Only deviate when you have a good reason to do so.
The lazy man's guide to SQL database design, but Jim Nasby.
;-)
--Josh
---(end of broadcast)---
TIP 3: Have you
On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
So ISTM it's much easier to just use surrogate keys and be
done with it. Only deviate when you have a good reason to do so.
The lazy man's guide to SQL database design, but Jim Nasby.
;-)
Hehe... I was thinking the same thing. I've
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Josh Berkus
Sent: Thursday, January 19, 2006 10:09 AM
To: Martijn van Oosterhout
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
Martjin
On Thu, Jan 19, 2006 at 02:01:14PM -0500, [EMAIL PROTECTED] wrote:
On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
So ISTM it's much easier to just use surrogate keys and be
done with it. Only deviate when you have a good reason to do so.
The lazy man's guide to SQL database
On Thu, Jan 19, 2006 at 11:22:24AM -0800, Dann Corbit wrote:
In any of these either misspellings, changes of names, ownership or
even structure over time render the obvious useless as keys. There
are
techniques for detecting and reducing duplication but the point is
that
for any of
On Thu, Jan 19, 2006 at 01:48:18PM -0600, Jim C. Nasby wrote:
On Thu, Jan 19, 2006 at 02:01:14PM -0500, [EMAIL PROTECTED] wrote:
On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
So ISTM it's much easier to just use surrogate keys and be
done with it. Only deviate when you
On Thu, Jan 19, 2006 at 10:09:26AM -0800, Josh Berkus wrote:
Martjin,
In any of these either misspellings, changes of names, ownership or
even structure over time render the obvious useless as keys. There are
techniques for detecting and reducing duplication but the point is that
for any of
Yes. Representation of the DNA is probably best. But - that's a lot of
data to use as a key in multiple tables. :-)
No then you have problems with identical twins :)
Chris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
On Jan 20, 2006, at 10:50 , Christopher Kings-Lynne wrote:
Yes. Representation of the DNA is probably best. But - that's a
lot of
data to use as a key in multiple tables. :-)
No then you have problems with identical twins :)
And, looking forward, clones.
Michael Glaesemann
grzm
Jim C. Nasby jnasby at pervasive.com writes:
a) the optimizer does a really poor job on multi-column index statistics
So it should be fixed?
And there are a *lot* of singular, natural keys.
b) If each parent record will have many children, the space savings from
using a surrogate key can
Greg Stark gsstark at mit.edu writes:
I hate knee-jerk reactions too, but just think of all the pain of people
dealing with databases where they used Social Security numbers for primary
keys. I would never use an attribute that represents some real-world datum as
a primary key any more.
I am
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimarães Faria Corcete DUTRA
wrote:
Jim C. Nasby jnasby at pervasive.com writes:
Generally, I just use surrogate keys for everything unless performance
dictates something else.
What I am proposing is the reverse: use natural keys for
On Jan 18, 2006, at 22:08 , Leandro Guimarães Faria Corcete DUTRA wrote:
Jim C. Nasby jnasby at pervasive.com writes:
a) the optimizer does a really poor job on multi-column index
statistics
So it should be fixed?
Of course! Patches welcome!
Michael Glaesemann
grzm myrealbox com
Leandro Guimarães Faria Corcete DUTRA [EMAIL PROTECTED] writes:
Greg Stark gsstark at mit.edu writes:
I hate knee-jerk reactions too, but just think of all the pain of people
dealing with databases where they used Social Security numbers for primary
keys. I would never use an attribute
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimar??es Faria Corcete
DUTRA wrote:
b) If each parent record will have many children, the space savings from
using a surrogate key can be quite large
Not such a common case.
Hmmm...
Many blog entries per user... Many blog comments per
Ooops, fat-finger'd -hackers...
-Original Message-
Adding -hackers back to the list.
From: Leandro Guimarães Faria Corcete Dutra
Em Seg, 2006-01-16 às 12:52 -0600, Jim C. Nasby escreveu:
On Sat, Jan 14, 2006 at 07:28:21PM +0900, Michael Glaesemann wrote:
For UPDATEs and
Comparing two ints is much, much faster than comparing two text
fields. For a small number of comparisons, it doesn't matter. When
you're joining tables together, it's a different story.
That is where data independence would come handy... like a better enum,
with possreps and hidden
Martjin,
Interesting. However, in my experience very few things have natural
keys. There are no combination of attributes for people, phone calls
or even real events that make useful natural keys.
I certainly hope that I never have to pick up one of your projects. A
table without a natural
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Josh Berkus
Sent: Wednesday, January 18, 2006 3:59 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
Martjin,
Interesting. However, in my
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Dann Corbit
Sent: Wednesday, January 18, 2006 4:04 PM
To: josh@agliodbs.com; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
-Original Message
Em Qua, 2006-01-18 às 17:22 -0600, Jim C. Nasby escreveu:
Forgive me my ignorance, but are ints inherently faster to compare than
strings, or is it just an implementation detail? Ideally, if this is so
a fully data-independent system would create a hash behind the back of
user in order
On Jan 19, 2006, at 9:31 , Leandro Guimarães Faria Corcete Dutra wrote:
OK. Again, data independence should be the goal here.
snip /
Again, implementation details... levels mixup.
snip /
Lack of data implementation biting us again.
snip /
Yet real
PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Leandro Guimarães Faria Corcete Dutra
Sent: Wednesday, January 18, 2006 4:31 PM
To: Jim C. Nasby
Cc: pgsql-hackers@postgresql.org
Subject: Re: FW: [HACKERS] Surrogate keys (Was: enums)
Em Qua, 2006-01-18 às 17:22 -0600, Jim C
On Jan 19, 2006, at 10:34 , Dann Corbit wrote:
http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf
PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be
assigned by the DBMS only if a user-defined primary key is not
available.
snip /
An immutable primary key has an extra
-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 18, 2006 5:48 PM
To: Dann Corbit
Cc: Leandro Guimarães Faria Corcete Dutra; Jim C. Nasby; pgsql-
[EMAIL PROTECTED]
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
On Jan 19, 2006
Dann,
The primary key should be immutable, meaning that its value should not be
changed during the course of normal operations of the database.
Why? I don't find this statement to be self-evident. Why would we have ON
UPDATE CASCADE if keys didn't change sometimes?
At any rate, the use
Michael Glaesemann [EMAIL PROTECTED] writes:
As far as I can tell, the only difference between your position,
Dann, and Date and Darwen's, is that you think no natural key is
immutable.
DD's examples of natural keys are worth a second look though:
If a primary key exists for a collection
If a primary key exists for a collection that is known never to change,
for example social security number, student registration number, or
employee number, then no additional system-assigned UID is required.
In point of fact Social security numbers *can* change.
--
greg
On Wed, Jan 18, 2006 at 03:58:50PM -0800, Josh Berkus wrote:
Martjin,
Interesting. However, in my experience very few things have natural
keys. There are no combination of attributes for people, phone calls
or even real events that make useful natural keys.
I certainly hope that I
On Sat, Jan 14, 2006 at 07:28:21PM +0900, Michael Glaesemann wrote:
On Jan 13, 2006, at 21:42 , Leandro Guimar?es Faria Corcete DUTRA wrote:
If you still declare the natural key(s) as UNIQUEs, you have just made
performance worse. Now there are two keys to be checked on UPDATEs
and
Rod Taylor pg at rbt.ca writes:
The basic idea is that most of us break out schemas by creating fake
primary keys for the purpose of obtaining performance because using the
proper primary key (single or multiple columns) is often very slow.
This is one thing I simply can't understand.
If you
On Jan 13, 2006, at 21:42 , Leandro Guimarães Faria Corcete DUTRA wrote:
If you still declare the natural key(s) as UNIQUEs, you have just made
performance worse. Now there are two keys to be checked on UPDATEs
and
INSERTs, two indexes to be updated, and probably a SEQUENCE too.
For
Michael Glaesemann wrote:
On Jan 13, 2006, at 21:42 , Leandro Guimarães Faria Corcete DUTRA wrote:
If you still declare the natural key(s) as UNIQUEs, you have just made
performance worse. Now there are two keys to be checked on UPDATEs and
INSERTs, two indexes to be updated, and probably a
On Fri, 2006-01-13 at 12:42 +, Leandro Guimarães Faria Corcete DUTRA
wrote:
Rod Taylor pg at rbt.ca writes:
The basic idea is that most of us break out schemas by creating fake
primary keys for the purpose of obtaining performance because using the
proper primary key (single or
Rod Taylor wrote:
If you still declare the natural key(s) as UNIQUEs, you have just made
performance worse. Now there are two keys to be checked on UPDATEs and
INSERTs, two indexes to be updated, and probably a SEQUENCE too.
Indeed. Using a surrogate key is not free and that is why it would
On Fri, Jan 13, 2006 at 12:42:55PM +, Leandro Guimarães Faria Corcete DUTRA
wrote:
Rod Taylor pg at rbt.ca writes:
The basic idea is that most of us break out schemas by creating fake
primary keys for the purpose of obtaining performance because using the
proper primary key (single or
On Sat, Jan 14, 2006 at 11:06:07AM -0500, [EMAIL PROTECTED] wrote:
Not to completely defend the practice - but in some applications,
INSERT is much less frequent than UPDATE, and that UPDATE requires a
unique check on the primary key and the surrogate key, as well as an
update, should be
On Sat, Jan 14, 2006 at 09:33:39PM +0100, Martijn van Oosterhout wrote:
On Sat, Jan 14, 2006 at 11:06:07AM -0500, [EMAIL PROTECTED] wrote:
Not to completely defend the practice - but in some applications,
INSERT is much less frequent than UPDATE, and that UPDATE requires a
unique check on
Leandro Guimarães Faria Corcete DUTRA [EMAIL PROTECTED] writes:
Certainly decoupling presentation from storage would be nice, but even before
that generalised use of surrogate keys seems to me a knee-jerk reaction.
I hate knee-jerk reactions too, but just think of all the pain of people
49 matches
Mail list logo
