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 yo
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 goin
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 1/21/06, Christopher Browne <[EMAIL PROTECTED]> wrote:
> > 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 no
> 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 a
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 uniqu
On Fri, Jan 20, 2006 at 01:58:16PM +, Richard Huxton wrote:
> Aside:
> Even if not using name+address as a primary key, a separate record
> should be kept of these details *at the time of the invoice* otherwise
> you'll never be able to match up a printed invoice with its digital
> source. U
Dann Corbit wrote:
When the data changes, the problems generated are not just due to
repercussions related to the child and parent tables related through the
primary key.
Someone has an invoice, and they call in with a question. A combination
of their name and address was used as a primary key.
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 myrealbox
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 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
> >f
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 wh
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
> > >
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 d
> -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
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 thin
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 y
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 where
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 eith
[EMAIL PROTECTED] writes:
> 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
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 u
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 ti
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
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.
>
> D&D's examples of "natural" keys a
Em Qui, 2006-01-19 às 09:54 +0900, Michael Glaesemann escreveu:
> On Jan 19, 2006, at 9:31 , Leandro Guimarães Faria Corcete Dutra wrote:
>
> If these are things
> you're interested in (and it certainly appears you are), why not
> contribute?
'Cause unfortunately am not a hacker...
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
> >> 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
--
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.
D&D's examples of "natural" keys are worth a second look though:
>> If a primary key exists for a c
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 us
> -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:
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.
An immutable primary key has an extra advantage
Original Message-
> From: [EMAIL 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
On Jan 19, 2006, at 9:31 , Leandro Guimarães Faria Corcete Dutra wrote:
OK. Again, data independence should be the goal here.
Again, implementation details... levels mixup.
Lack of data implementation biting us again.
Yet real user-defined data ty
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
> -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)
&g
> -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,
>
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 nat
>> 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 hi
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 a
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
Leandro Guimarães Faria Corcete DUTRA <[EMAIL PROTECTED]> writes:
> Greg Stark 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 t
On Jan 18, 2006, at 22:08 , Leandro Guimarães Faria Corcete DUTRA wrote:
Jim C. Nasby 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
---
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimarães Faria Corcete DUTRA
wrote:
> Jim C. Nasby 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 everyt
Greg Stark 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 not f
Jim C. Nasby 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 be quit
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
> >
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
d
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 chec
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 co
On Fri, Jan 13, 2006 at 12:42:55PM +, Leandro Guimarães Faria Corcete DUTRA
wrote:
> Rod Taylor 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 o
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, 2006-01-13 at 12:42 +, Leandro Guimarães Faria Corcete DUTRA
wrote:
> Rod Taylor 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 mul
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 S
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 UPDAT
Rod Taylor 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 s
55 matches
Mail list logo
