primary key and existing unique fields

#11

Quote:

That article makes me want to vomit uncontrollably! ;-)

"Business data might also simply be bad -- glitches in the Social
Security Administration's system may lead to different persons having
the same Social Security Number. A surrogate key helps to isolate the
system from such problems."

The surrogate key isn't solving the underlying logical inconsistency
problem. It is being used as a work-around to cover one up. I suspect
the author of being a MySQL user.

I think what he's saying is that an application bug, or a business
process problem, should not interfere with your database system.
Granted, two identical SSNs seems far fetched. However, if your business
screws up and you need to change someone's primary key, you've just
violated the principle of a primary key. You better be REALLY sure the
primary key will NEVER change over time for a given record, and that it
really is unique.

An SSN might fit that description, but there are always strange
situations. What if someone sues to have their SSN changed and a judge
orders it? If that's their PK, the social security administration is up
a creek (at least in the DB theory world, it probably wouldn't matter
much in practice).

Regards,
Jeff Davis

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#12

On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote:

Quote:

joking

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

/joking

mmmmm.. string theory. :-)

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBfuZH0QaQZBaqXgwRAvOiAKDI1ujd/aX0/tANKP6o6fovVbOFHgCgmIhp
dZcfLNfnyIbHnGd2hZVJ1W0=
=6NiU
-----END PGP SIGNATURE-----

#13

On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell <robby (AT) planetargon (DOT) com> wrote:

Quote:

On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote:
joking

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

/joking

mmmmm.. string theory. :-)

Ya you know the theory that states that the Database is really made up
of a large amount of strings. Some are even null terminated strings,
although most strings really have a quanta that can be found immediate
before the string. :-)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#14

On Tue, 2004-10-26 at 22:03 -0500, Kevin Barnard wrote:

Quote:

On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell <robby (AT) planetargon (DOT) com>wrote:
On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote:
joking

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

/joking

mmmmm.. string theory. :-)

Ya you know the theory that states that the Database is really made up
of a large amount of strings. Some are even null terminated strings,
although most strings really have a quanta that can be found immediate
before the string. :-)

How do we SELECT the string so that we can observe it then? ;-)

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBfx9M0QaQZBaqXgwRAr+KAJ9t0YeaoJvcWXuFKRLDgt vZuams0ACfVxpr
BJvE7McTkL7J1E6nsn0mbxk=
=45xy
-----END PGP SIGNATURE-----

#15

-----Original Message-----
From: Robby Russell [mailto:robby (AT) planetargon (DOT) com]
Sent: Tue 10/26/2004 9:08 PM
To: Kevin Barnard
Cc: pgsql-general (AT) postgresql (DOT) org
Subject: Re: [GENERAL] primary key and existing unique fields
On Tue, 2004-10-26 at 22:03 -0500, Kevin Barnard wrote:

Quote:

On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell <robby (AT) planetargon (DOT) com>wrote:
On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote:
joking

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

/joking

mmmmm.. string theory. :-)

Ya you know the theory that states that the Database is really made up
of a large amount of strings. Some are even null terminated strings,
although most strings really have a quanta that can be found immediate
before the string. :-)

How do we SELECT the string so that we can observe it then? ;-)

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
****************************************/

You can't observe it ... only *infer* it.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#16

Sally Sally wrote:

Quote:

This
existing unique field will have to be a character of fixed length
(VARCHAR(12)) because although it's a numeric value there will be
leading zeroes.

Plenty of people are contributing their tuppence-worth regarding the
choice of surrogate vs natural primary key.

Can I just point out that your existing unique field is EITHER a numeric
value OR it has a fixed number of characters - numbers don't have
leading zeros.

If what you have is a number, then perhaps consider int8/numeric types
and format appropriately when you display the values.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#17

On Wed, Oct 27, 2004 at 00:10:27 +0200,
Dawid Kuroczko <qnex42 (AT) gmail (DOT) com> wrote:

Quote:

3. If you'll need things like "last 50 keys", you can SELECT * FROM
foo ORDER BY yourserialkey DESC LIMIT 50;

You really shouldn't be doing that if you are using sequences to generate
the key. Sequences are just guarenteed to return unique values, not to
return them in order. Because groups of sequences can be allocated to
a backend at once depending on a setting settable by a client, you can
get assignments out of order. Also for overlapping transactions what
the application means by the last 50 entries may not match what you
get when you get the 50 highest serial values.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#18

Dawid,
I am interested in the first point you made that:
having varchar(12) in every referencing table, takes more storage
space.
The thing is though, if I have a serial primary key then it would be an
additional column. Or you are saying the space taken by a VARCHAR(12) field
is more than two INT fields? ( or is it the fact that when it is referenced
it will appear several times?) I guess the reason I am resisting the idea of
an additional primary key field is to avoid the additional lookup in some
queries. Perhaps it's a minor almost irrelevant performance factor.
Thanks
Sally

__________________________________________________ _______________
Is your PC infected? Get a FREE online computer virus scan from McAfee®
Security. http://clinic.mcafee.com/clinic/ibuy...n.asp?cid=3963

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#19

On Thu, Oct 28, 2004 at 14:31:32 +0000,
Sally Sally <dedeb17 (AT) hotmail (DOT) com> wrote:

Quote:

Dawid,
I am interested in the first point you made that:
having varchar(12) in every referencing table, takes more storage
space.
The thing is though, if I have a serial primary key then it would be an
additional column. Or you are saying the space taken by a VARCHAR(12) field
is more than two INT fields? ( or is it the fact that when it is referenced
it will appear several times?) I guess the reason I am resisting the idea
of an additional primary key field is to avoid the additional lookup in
some queries. Perhaps it's a minor almost irrelevant performance factor.

I think it is better to worry about what is going to make it easiest to
have clean data and to support future changes than worry about performance.
Over the long run hardware is cheaper than people.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#20

I think the same too but sometimes it seems in the real world performance is
given more value than a properly designed db. Or the long term flexiblity is
not taken into account given the short term requirements.
regards
Sally

Quote:

From: Bruno Wolff III <bruno (AT) wolff (DOT) to
To: Sally Sally <dedeb17 (AT) hotmail (DOT) com
CC: pgsql-general (AT) postgresql (DOT) org, qnex42 (AT) gmail (DOT) com
Subject: Re: [GENERAL] primary key and existing unique fields
Date: Thu, 28 Oct 2004 12:44:00 -0500

On Thu, Oct 28, 2004 at 14:31:32 +0000,
Sally Sally <dedeb17 (AT) hotmail (DOT) com> wrote:
Dawid,
I am interested in the first point you made that:
having varchar(12) in every referencing table, takes more storage
space.
The thing is though, if I have a serial primary key then it would be an
additional column. Or you are saying the space taken by a VARCHAR(12)
field
is more than two INT fields? ( or is it the fact that when it is
referenced
it will appear several times?) I guess the reason I am resisting the
idea
of an additional primary key field is to avoid the additional lookup in
some queries. Perhaps it's a minor almost irrelevant performance factor.

I think it is better to worry about what is going to make it easiest to
have clean data and to support future changes than worry about performance.
Over the long run hardware is cheaper than people.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

__________________________________________________ _______________
Check out Election 2004 for up-to-date election news, plus voter tools and
more! http://special.msn.com/msn/election2004.armx

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster